Re: Parameter for Stored Procedure
- From: "Dan Guzman" <guzmanda@xxxxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Sat, 16 Jun 2007 06:53:09 -0500
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@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'.
.
- Prev by Date: Re: Dynamic database selection?
- Next by Date: Re: Deadlock within stored procedure - need help
- Previous by thread: Re: Awesome
- Next by thread: Re: Parameter for Stored Procedure
- Index(es):
Relevant Pages
|
