Re: stored procedure

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: jonjo (jonjo_at_discussions.microsoft.com)
Date: 10/29/04 

Date: Fri, 29 Oct 2004 08:28:03 -0700

The reason for this is to take into account different variables such as i.e
period 1 2004 = accounts20041, period 2 2004 =accounts20042 etc. It's
actually a bit more involved that the example I've just given but I think you
understand?

So is it possible to create a stored procedure with an input variable which
allows the creation of this?

"Tibor Karaszi" wrote:

> What I'm questioning is the design behind the need to create new databases all the time. Why do they
> do this?
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
>
> "jonjo" <jonjo@discussions.microsoft.com> wrote in message
> news:E401618E-A5E6-4159-9FE9-A09E0C06B453@microsoft.com...
> > No I am not a hosting company. I want to do this to enable an internal dept
> > in my organisation to automate the creation of databases. Currently they have
> > to create new ones all the time and I just want to save them some time.
> >
> > "Tibor Karaszi" wrote:
> >
> >> The big question boils down to design. For what purpose do you create these databases? Are you a
> >> hosting company that allow your customers to store whatever tables etc they want inside the
> >> databases you create for them? Of do you want to keep track of your customers , if so, you
> >> shouldn't
> >> create a new database for each customer (and not even a table for each customer).
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >>
> >>
> >> "jonjo" <jonjo@discussions.microsoft.com> wrote in message
> >> news:B1A2F0C6-7B66-48A0-9A86-7861A7720E1A@microsoft.com...
> >> > Hi
> >> >
> >> > Thanks for the reply. Perhaps you can think of a better way for me to do
> >> > this. Basically I want to automate the creation of databases that might just
> >> > have a different ending e.g. accounts1, accounts2, accounts3 etc. I thought I
> >> > might be able to do this by creating a parameterised sp which would let the
> >> > user just type in the name of the db they want to create and then run the sp
> >> > to create that db.
> >> >
> >> > Is that possible or is there a better way?
> >> >
> >> > Thanks
> >> >
> >> > "Tibor Karaszi" wrote:
> >> >
> >> >> Seems you can't have a variable for the database name in CREATE DATABASE. You would have to
> >> >> build
> >> >> the whole CRE¤ATE DATABASE command in a variable and use EXEC(@sql) to execute the statement.
> >> >>
> >> >> But why do you want to create a database from inside a stored procedure?
> >> >>
> >> >> --
> >> >> Tibor Karaszi, SQL Server MVP
> >> >> http://www.karaszi.com/sqlserver/default.asp
> >> >> http://www.solidqualitylearning.com/
> >> >>
> >> >>
> >> >> "jonjo" <jonjo@discussions.microsoft.com> wrote in message
> >> >> news:60BC3CF7-432C-4AA5-B978-5B7D5663CC49@microsoft.com...
> >> >> > Hi
> >> >> >
> >> >> > I have taken away the USE statement but when I take the brackets away from
> >> >> > the @dbname variable in the CREATE DATABASE statement it gives me :msg 170
> >> >> > incorrect syntax near dbname
> >> >> >
> >> >> >
> >> >> >
> >> >> > "Tibor Karaszi" wrote:
> >> >> >
> >> >> >> 1. The USE doesn't do anything for you. The database doesn't exist so the use will fail,
> >> >> >> and
> >> >> >> the
> >> >> >> use
> >> >> >> is done inside EXEC so it is in its own scope.
> >> >> >>
> >> >> >> 2. Don't put brackets around the variable holding the database name in the CREATE DATABASE
> >> >> >> command.
> >> >> >>
> >> >> >> If you still don't get it to work, please also post the error message.
> >> >> >>
> >> >> >> --
> >> >> >> Tibor Karaszi, SQL Server MVP
> >> >> >> http://www.karaszi.com/sqlserver/default.asp
> >> >> >> http://www.solidqualitylearning.com/
> >> >> >>
> >> >> >>
> >> >> >> "jonjo" <jonjo@discussions.microsoft.com> wrote in message
> >> >> >> news:5182DD96-53AD-4223-9352-A944AFE1F47A@microsoft.com...
> >> >> >> > Hi
> >> >> >> >
> >> >> >> > I'm making a bit of progress but I think I'm passing the wrong parameters in
> >> >> >> > the procedure. This is what I am trying to do but when I enter the parameter
> >> >> >> > it is not feeding thru? I'm obviously doing something wrong along the line so
> >> >> >> > any help would be appreciated.
> >> >> >> >
> >> >> >> > CREATE PROCEDURE TEST2 @dbname nvarchar(10) AS
> >> >> >> > BEGIN
> >> >> >> > DECLARE @quotedbname nvarchar(10)
> >> >> >> > SET @quotedbname= quotename (@dbname)
> >> >> >> >
> >> >> >> > EXEC ('
> >> >> >> > USE '+ @quotedbname + '')
> >> >> >> >
> >> >> >> > CREATE DATABASE [@dbname] ON (NAME = N'@dbname_Data', FILENAME =
> >> >> >> > N'e:\Program Files\Microsoft SQL Server\MSSQL\data\@dbname_Data.MDF' , SIZE =
> >> >> >> > 1, FILEGROWTH = 10%) LOG ON (NAME = N'@dbname_Log', FILENAME = N'e:\Program
> >> >> >> > Files\Microsoft SQL Server\MSSQL\data\@dbname_Log.LDF' , SIZE = 1, FILEGROWTH
> >> >> >> > = 10%)
> >> >> >> > COLLATE Latin1_General_CI_AS
> >> >> >> >
> >> >> >> > exec sp_dboption N'@dbname', N'autoclose', N'false'
> >> >> >> >
> >> >> >> > exec sp_dboption N'@dbname', N'bulkcopy', N'false'
> >> >> >> >
> >> >> >> > exec sp_dboption N'@dbname', N'trunc. log', N'true'
> >> >> >> >
> >> >> >> > exec sp_dboption N'@dbname', N'torn page detection', N'false'
> >> >> >> >
> >> >> >> > exec sp_dboption N'@dbname', N'read only', N'false'
> >> >> >> >
> >> >> >> > exec sp_dboption N'@dbname', N'dbo use', N'false'
> >> >> >> >
> >> >> >> > exec sp_dboption N'@dbname', N'single', N'false'
> >> >> >> >
> >> >> >> > exec sp_dboption N'@dbname', N'autoshrink', N'false'
> >> >> >> >
> >> >> >> > exec sp_dboption N'@dbname', N'ANSI null default', N'false'
> >> >> >> >
> >> >> >> > exec sp_dboption N'@dbname', N'recursive triggers', N'false'
> >> >> >> >
> >> >> >> > exec sp_dboption N'@dbname', N'ANSI nulls', N'false'
> >> >> >> >
> >> >> >> > exec sp_dboption N'@dbname', N'concat null yields null', N'false'
> >> >> >> >
> >> >> >> > exec sp_dboption N'@dbname', N'cursor close on commit', N'false'
> >> >> >> >
> >> >> >> > exec sp_dboption N'@dbname', N'default to local cursor', N'false'
> >> >> >> >
> >> >> >> > exec sp_dboption N'@dbname', N'quoted identifier', N'false'
> >> >> >> >
> >> >> >> > exec sp_dboption N'@dbname', N'ANSI warnings', N'false'
> >> >> >> >
> >> >> >> > exec sp_dboption N'@dbname', N'auto create statistics', N'false'
> >> >> >> >
> >> >> >> > exec sp_dboption N'@dbname', N'auto update statistics', N'false'
> >> >> >> >
> >> >> >> >
> >> >> >> > if( ( (@@microsoftversion / power(2, 24) = 8) and (@@microsoftversion &
> >> >> >> > 0xffff >= 724) ) or ( (@@microsoftversion / power(2, 24) = 7) and
> >> >> >> > (@@microsoftversion & 0xffff >= 1082) ) )
> >> >> >> > exec sp_dboption N'@dbname', N'db chaining', N'false'
> >> >> >> > END
> >> >> >> > GO
> >> >> >> >
> >> >> >> > "GYK" wrote:
> >> >> >> >
> >> >> >> >> You need to pass database name as a variable... Following script might be
> >> >> >> >> useful for you..
> >> >> >> >>
> >> >> >> >> CREATE PROCEDURE p_test_proc
> >> >> >> >> @dbname nvarchar(256)
> >> >> >> >> AS
> >> >> >> >> BEGIN
> >> >> >> >> -- Quote the database name with brackets
> >> >> >> >> DECLARE @quoteddbname nvarchar(256)
> >> >> >> >> SET @quoteddbname = quotename( @dbname )
> >> >> >> >>
> >> >> >> >> EXEC('
> >> >> >> >> USE '+ @quoteddbname +'
> >> >> >> >> DECLARE .....................
> >> >> >> >> .............................
> >> >> >> >> ............................ ')
> >> >> >> >> END
> >> >> >> >>
> >> >> >> >> HTH
> >> >> >> >> GYK
> >> >> >> >>
> >> >> >> >>
> >> >> >> >> "jonjo" wrote:
> >> >> >> >>
> >> >> >> >> > Hi
> >> >> >> >> >
> >> >> >> >> > I'm trying to create a stored procedure that will create a blank database
> >> >> >> >> > and then create certain tables. The create database works fine but I then
> >> >> >> >> > want to create the tables in the database which I have just created. I tried
> >> >> >> >> > putting in the 'use accounts' (accounts being the name of the db I just
> >> >> >> >> > created) in the stored procedure but this is obviously not allowed as a msg
> >> >> >> >> > returns:-
> >> >> >> >> >
> >> >> >> >> > error 154 a use database statement is not allowed in a procedure or trigger
> >> >> >> >> >
> >> >> >> >> > If I run the script in QA it works without any problem. Any ideas as how to
> >> >> >> >> > get round this?
> >> >> >> >> >
> >> >> >> >> > Thanks in advance
> >> >> >> >> >
> >> >> >> >> >
> >> >> >>
> >> >> >>
> >> >> >>
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>

Relevant Pages

  • Re: Atheists are the biggest fools on Earth
    ... historical account of the creation of the world. ... >>> same island), it would be easier. ... >>> proteins to be sequenced for a large sample of species. ... What objective reason could you have? ...
    (talk.origins)
  • Re: Strange file appeared in my home directory
    ... The creation date was about four ... I quickly checked my system with the native FreeBSD tool "chkrootkit". ... >>Yesterday my internet connection went down without a particular reason. ...
    (freebsd-questions)
  • Re: In the News: Tenn. AG: No constitutional concerns with
    ... about the creation of things, ... say that science has an answer for everything? ... and will never have a scientific answer for how life started. ... We just have no reason to put one in...The simple fact is that science does ...
    (talk.origins)
  • Re: Ken Ham and "Gods Gospel Lizards"
    ... In this account, God creats a separate being called ... 'Wisdom', who is identified as female, and the creation of whom ...
    (talk.origins)
  • [FUD4] Re: Decision on coom and cooa policy on eComStation
    ... >> That was part of the reason for its creation (the exclusion of various ... I've been reading a large percentage of his postings in these newsgroups ... Given the sheer number of falsehoods and wild exaggerations that Tim ...
    (comp.os.os2.misc)