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

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 (AT) CCC (DOT) HSHS.ORG> wrote

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

On Jun 16, 6:53 am, "Dan Guzman" <guzma...@nospam-
online.sbcglobal.net> wrote:

Now I get a different syntax problem.
Msg 102, Level 15, State 1, Procedure sp_CreateNewClientDb, Line 6
Incorrect syntax near 'QUOTENAME'.

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" <DBARNETT (AT) CCC (DOT) HSHS.ORG> wrote

On Jun 16, 6:53 am, "Dan Guzman" <guzma...@nospam-
online.sbcglobal.net> wrote:

Now I get a different syntax problem.
Msg 102, Level 15, State 1, Procedure sp_CreateNewClientDb, Line 6
Incorrect syntax near 'QUOTENAME'.

On Jun 16, 8:27 am, "Dan Guzman" <guzma...@nospam-
online.sbcglobal.net> wrote:
Thank you very much. It works great...
Now I'll try to fight my way through building tables using the
variable name.

Can you explain your thinking on this? A schema is a realization of a
data model and you ought to know the name and particulars of it before
you create it. But you want to have a user create a whole new universe
on the fly. It is bad enough when people write dynamic SQL to build
tables on the fly -- the logical equivalent of magically producing
elephants from the sky.