Re: Parameter for Stored Procedure
- From: "Dan Guzman" <guzmanda@xxxxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Sat, 16 Jun 2007 08:27:59 -0500
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" <DBARNETT@xxxxxxxxxxxx> wrote in message news:1181995691.999232.150000@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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...@xxxxxxxxxxxx> wrote in message
news:1181993307.112897.87820@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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'.
.
- References:
- Re: Parameter for Stored Procedure
- From: Dan Guzman
- Re: Parameter for Stored Procedure
- Prev by Date: sql server 2000 connection fail
- Next by Date: Re: how much bytes needed in sql server
- Previous by thread: Re: Parameter for Stored Procedure
- Next by thread: Re: Parameter for Stored Procedure
- Index(es):
Relevant Pages
|
