Posted by Dan Guzman on 06/16/07 11:53

> 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" <DBARNETT@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'.

[Back to original message]