Posted by Dennis on 06/16/07 14:18

On Jun 16, 8:27 am, "Dan Guzman" <guzma...@nospam-
online.sbcglobal.net> wrote:
> > Msg 102, Level 15, State 1, Procedure sp_CreateNewClientDb, Line 4
> > Incorrect syntax near '@ClientDBName'.
>
> Sorry, I tested with PRINT instead of EXECUTE and forgot that the a variable
> is needed for EXECUTE. Try
>
> CREATE PROCEDURE [dbo].[sp_CreateNewClientDb]
> @ClientDBName sysname
> AS
> DECLARE @SqlStatement nvarchar(4000)
> SET @SqlStatement =
> 'CREATE DATABASE ' + QUOTENAME(@ClientDBName) + ' ON PRIMARY
> ( NAME = N''' + @ClientDBName + ''',
> FILENAME = N''C:\Program Files\Microsoft SQL
> Server\MSSQL.2\MSSQL\DATA\' + @ClientDBName + '.mdf'' ,
> SIZE = 11264KB ,
> MAXSIZE = UNLIMITED,
> FILEGROWTH = 1024KB )
> LOG ON
> ( NAME = N''' + @ClientDBName + '_log'',
> FILENAME = N''C:\Program Files\Microsoft SQL
> Server\MSSQL.2\MSSQL\DATA\' + @ClientDBName + '_log.ldf'' ,
> SIZE = 1024KB ,
> MAXSIZE = 2048GB ,
> FILEGROWTH = 10%)
> COLLATE SQL_Latin1_General_CP1_CI_AS'
> EXEC (@SqlStatement)
> GO
>
> --
> Hope this helps.
>
> Dan Guzman
> SQL Server MVP
>
> "Dennis" <DBARN...@CCC.HSHS.ORG> wrote in message
>
> news:1181995691.999232.150000@q75g2000hsh.googlegroups.com...
> On Jun 16, 6:53 am, "Dan Guzman" <guzma...@nospam-
>
>
>
>
>
> online.sbcglobal.net> wrote:
> > > I'm trying to alter my stored procedure to take a parameter for the
> > > Database Name, but as usual the syntax is killing me.
>
> > The database name must be a constant instead of a variable. You'll need
> > instead build and execute the create statement dynamically:
>
> > CREATE PROCEDURE [dbo].[sp_CreateNewClientDb]
> > @ClientDBName sysname
> > AS
> > EXECUTE
> > (
> > 'CREATE DATABASE ' + QUOTENAME(@ClientDBName) + ' ON PRIMARY
> > ( NAME = N''' + @ClientDBName + ''',
> > FILENAME = N''C:\Program Files\Microsoft SQL
> > Server\MSSQL.2\MSSQL\DATA\' + @ClientDBName + '.mdf'' ,
> > SIZE = 11264KB ,
> > MAXSIZE = UNLIMITED,
> > FILEGROWTH = 1024KB )
> > LOG ON
> > ( NAME = N''' + @ClientDBName + '_log'',
> > FILENAME = N''C:\Program Files\Microsoft SQL
> > Server\MSSQL.2\MSSQL\DATA\' + @ClientDBName + '_log.ldf'' ,
> > SIZE = 1024KB ,
> > MAXSIZE = 2048GB ,
> > FILEGROWTH = 10%)
> > COLLATE SQL_Latin1_General_CP1_CI_AS'
> > )
> > GO
>
> > --
> > Hope this helps.
>
> > Dan Guzman
> > SQL Server MVP
>
> > "Dennis" <DBARN...@CCC.HSHS.ORG> wrote in message
>
> >news:1181993307.112897.87820@q75g2000hsh.googlegroups.com...
> > Hi
> > I'm trying to alter my stored procedure to take a parameter for the
> > Database Name, but as usual the syntax is killing me.
> > Thanks for any help
> > Dennis
>
> > '--------------------------------------------------------------------------­­­--------------------------------
> > Before - This Works without a paramater
> > '--------------------------------------------------------------------------­­­--------------------------------
>
> > set ANSI_NULLS ON
> > set QUOTED_IDENTIFIER ON
> > go
>
> > ALTER PROCEDURE [dbo].[sp_CreateNewClientDb]
> > AS
> > CREATE DATABASE [MyClientDatabase] ON PRIMARY
> > ( NAME = N'MyClientDatabase',
> > FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\DATA
> > \MyClientDatabase.mdf' ,
> > SIZE = 11264KB ,
> > MAXSIZE = UNLIMITED,
> > FILEGROWTH = 1024KB )
> > LOG ON
> > ( NAME = N'MyClientDatabase_log',
> > FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\DATA
> > \MyClientDatabase_log.ldf' ,
> > SIZE = 1024KB ,
> > MAXSIZE = 2048GB ,
> > FILEGROWTH = 10%)
> > COLLATE SQL_Latin1_General_CP1_CI_AS
>
> > '--------------------------------------------------------------------------­­­--------------------------------
> > After - This Doesn't work with a parameter
> > '--------------------------------------------------------------------------­­­--------------------------------
>
> > set ANSI_NULLS ON
> > set QUOTED_IDENTIFIER ON
> > go
>
> > ALTER PROCEDURE [dbo].[sp_CreateNewClientDb]
> > AS
> > CREATE DATABASE @ClientDBName ON PRIMARY
> > ( NAME = N@ClientDBName,
> > FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\DATA
> > \@ClientDBName' + '.mdf' ,
> > SIZE = 11264KB ,
> > MAXSIZE = UNLIMITED,
> > FILEGROWTH = 1024KB )
> > LOG ON
> > ( NAME = N'@ClientDBName' + '_log',
> > FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\DATA
> > \@ClientDBName' + '_log.ldf' ,
> > SIZE = 1024KB ,
> > MAXSIZE = 2048GB ,
> > FILEGROWTH = 10%)
> > COLLATE SQL_Latin1_General_CP1_CI_AS
>
> > Msg 102, Level 15, State 1, Procedure sp_CreateNewClientDb, Line 4
> > Incorrect syntax near '@ClientDBName'.
>
> Now I get a different syntax problem.
> Msg 102, Level 15, State 1, Procedure sp_CreateNewClientDb, Line 6
> Incorrect syntax near 'QUOTENAME'.- Hide quoted text -
>
> - Show quoted text -

Thank you very much. It works great... :)
Now I'll try to fight my way through building tables using the
variable name.

[Back to original message]