Posted by Dennis on 06/16/07 12:08

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'.

[Back to original message]