Parameter for Stored Procedure
Parameter for Stored Procedure
am 16.06.2007 13:28:27 von Dennis
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
'----------------------------------------------------------- ---------------=
=AD--------------------------------
Before - This Works without a paramater
'----------------------------------------------------------- ---------------=
=AD--------------------------------
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[sp_CreateNewClientDb]
AS
CREATE DATABASE [MyClientDatabase] ON PRIMARY
( NAME =3D N'MyClientDatabase',
FILENAME =3D N'C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\DATA
\MyClientDatabase.mdf' ,
SIZE =3D 11264KB ,
MAXSIZE =3D UNLIMITED,
FILEGROWTH =3D 1024KB )
LOG ON
( NAME =3D N'MyClientDatabase_log',
FILENAME =3D N'C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\DATA
\MyClientDatabase_log.ldf' ,
SIZE =3D 1024KB ,
MAXSIZE =3D 2048GB ,
FILEGROWTH =3D 10%)
COLLATE SQL_Latin1_General_CP1_CI_AS
'----------------------------------------------------------- ---------------=
=AD--------------------------------
After - This Doesn't work with a parameter
'----------------------------------------------------------- ---------------=
=AD--------------------------------
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[sp_CreateNewClientDb]
AS
CREATE DATABASE @ClientDBName ON PRIMARY
( NAME =3D N@ClientDBName,
FILENAME =3D N'C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\DATA
\@ClientDBName' + '.mdf' ,
SIZE =3D 11264KB ,
MAXSIZE =3D UNLIMITED,
FILEGROWTH =3D 1024KB )
LOG ON
( NAME =3D N'@ClientDBName' + '_log',
FILENAME =3D N'C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\DATA
\@ClientDBName' + '_log.ldf' ,
SIZE =3D 1024KB ,
MAXSIZE =3D 2048GB ,
FILEGROWTH =3D 10%)
COLLATE SQL_Latin1_General_CP1_CI_AS
Msg 102, Level 15, State 1, Procedure sp_CreateNewClientDb, Line 4
Incorrect syntax near '@ClientDBName'.
Re: Parameter for Stored Procedure
am 16.06.2007 13:53:09 von Dan Guzman
> 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" 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'.
Re: Parameter for Stored Procedure
am 16.06.2007 14:08:11 von Dennis
On Jun 16, 6:53 am, "Dan Guzman"
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 =3D N''' + @ClientDBName + ''',
> FILENAME =3D N''C:\Program Files\Microsoft SQL
> Server\MSSQL.2\MSSQL\DATA\' + @ClientDBName + '.mdf'' ,
> SIZE =3D 11264KB ,
> MAXSIZE =3D UNLIMITED,
> FILEGROWTH =3D 1024KB )
> LOG ON
> ( NAME =3D N''' + @ClientDBName + '_log'',
> FILENAME =3D N''C:\Program Files\Microsoft SQL
> Server\MSSQL.2\MSSQL\DATA\' + @ClientDBName + '_log.ldf'' ,
> SIZE =3D 1024KB ,
> MAXSIZE =3D 2048GB ,
> FILEGROWTH =3D 10%)
> COLLATE SQL_Latin1_General_CP1_CI_AS'
> )
> GO
>
> --
> Hope this helps.
>
> Dan Guzman
> SQL Server MVP
>
> "Dennis" 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 =3D N'MyClientDatabase',
> FILENAME =3D N'C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\DATA
> \MyClientDatabase.mdf' ,
> SIZE =3D 11264KB ,
> MAXSIZE =3D UNLIMITED,
> FILEGROWTH =3D 1024KB )
> LOG ON
> ( NAME =3D N'MyClientDatabase_log',
> FILENAME =3D N'C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\DATA
> \MyClientDatabase_log.ldf' ,
> SIZE =3D 1024KB ,
> MAXSIZE =3D 2048GB ,
> FILEGROWTH =3D 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 =3D N@ClientDBName,
> FILENAME =3D N'C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\DATA
> \@ClientDBName' + '.mdf' ,
> SIZE =3D 11264KB ,
> MAXSIZE =3D UNLIMITED,
> FILEGROWTH =3D 1024KB )
> LOG ON
> ( NAME =3D N'@ClientDBName' + '_log',
> FILENAME =3D N'C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\DATA
> \@ClientDBName' + '_log.ldf' ,
> SIZE =3D 1024KB ,
> MAXSIZE =3D 2048GB ,
> FILEGROWTH =3D 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'.
Re: Parameter for Stored Procedure
am 16.06.2007 15:27:59 von Dan Guzman
> 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" wrote in message
news:1181995691.999232.150000@q75g2000hsh.googlegroups.com.. .
On Jun 16, 6:53 am, "Dan Guzman"
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" 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'.
Re: Parameter for Stored Procedure
am 16.06.2007 16:18:07 von Dennis
On Jun 16, 8:27 am, "Dan Guzman"
online.sbcglobal.net> wrote:
> > 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 varia=
ble
> is needed for EXECUTE. Try
>
> CREATE PROCEDURE [dbo].[sp_CreateNewClientDb]
> @ClientDBName sysname
> AS
> DECLARE @SqlStatement nvarchar(4000)
> SET @SqlStatement =3D
> 'CREATE DATABASE ' + QUOTENAME(@ClientDBName) + ' ON PRIMARY
> ( NAME =3D N''' + @ClientDBName + ''',
> FILENAME =3D N''C:\Program Files\Microsoft SQL
> Server\MSSQL.2\MSSQL\DATA\' + @ClientDBName + '.mdf'' ,
> SIZE =3D 11264KB ,
> MAXSIZE =3D UNLIMITED,
> FILEGROWTH =3D 1024KB )
> LOG ON
> ( NAME =3D N''' + @ClientDBName + '_log'',
> FILENAME =3D N''C:\Program Files\Microsoft SQL
> Server\MSSQL.2\MSSQL\DATA\' + @ClientDBName + '_log.ldf'' ,
> SIZE =3D 1024KB ,
> MAXSIZE =3D 2048GB ,
> FILEGROWTH =3D 10%)
> COLLATE SQL_Latin1_General_CP1_CI_AS'
> EXEC (@SqlStatement)
> GO
>
> --
> Hope this helps.
>
> Dan Guzman
> SQL Server MVP
>
> "Dennis" wrote in message
>
> news:1181995691.999232.150000@q75g2000hsh.googlegroups.com.. .
> On Jun 16, 6:53 am, "Dan Guzman"
>
>
>
>
>
> 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 =3D N''' + @ClientDBName + ''',
> > FILENAME =3D N''C:\Program Files\Microsoft SQL
> > Server\MSSQL.2\MSSQL\DATA\' + @ClientDBName + '.mdf'' ,
> > SIZE =3D 11264KB ,
> > MAXSIZE =3D UNLIMITED,
> > FILEGROWTH =3D 1024KB )
> > LOG ON
> > ( NAME =3D N''' + @ClientDBName + '_log'',
> > FILENAME =3D N''C:\Program Files\Microsoft SQL
> > Server\MSSQL.2\MSSQL\DATA\' + @ClientDBName + '_log.ldf'' ,
> > SIZE =3D 1024KB ,
> > MAXSIZE =3D 2048GB ,
> > FILEGROWTH =3D 10%)
> > COLLATE SQL_Latin1_General_CP1_CI_AS'
> > )
> > GO
>
> > --
> > Hope this helps.
>
> > Dan Guzman
> > SQL Server MVP
>
> > "Dennis" 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
>
> > '----------------------------------------------------------- -----------=
----=AD--------------------------------
> > Before - This Works without a paramater
> > '----------------------------------------------------------- -----------=
----=AD--------------------------------
>
> > set ANSI_NULLS ON
> > set QUOTED_IDENTIFIER ON
> > go
>
> > ALTER PROCEDURE [dbo].[sp_CreateNewClientDb]
> > AS
> > CREATE DATABASE [MyClientDatabase] ON PRIMARY
> > ( NAME =3D N'MyClientDatabase',
> > FILENAME =3D N'C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\DATA
> > \MyClientDatabase.mdf' ,
> > SIZE =3D 11264KB ,
> > MAXSIZE =3D UNLIMITED,
> > FILEGROWTH =3D 1024KB )
> > LOG ON
> > ( NAME =3D N'MyClientDatabase_log',
> > FILENAME =3D N'C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\DATA
> > \MyClientDatabase_log.ldf' ,
> > SIZE =3D 1024KB ,
> > MAXSIZE =3D 2048GB ,
> > FILEGROWTH =3D 10%)
> > COLLATE SQL_Latin1_General_CP1_CI_AS
>
> > '----------------------------------------------------------- -----------=
----=AD--------------------------------
> > After - This Doesn't work with a parameter
> > '----------------------------------------------------------- -----------=
----=AD--------------------------------
>
> > set ANSI_NULLS ON
> > set QUOTED_IDENTIFIER ON
> > go
>
> > ALTER PROCEDURE [dbo].[sp_CreateNewClientDb]
> > AS
> > CREATE DATABASE @ClientDBName ON PRIMARY
> > ( NAME =3D N@ClientDBName,
> > FILENAME =3D N'C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\DATA
> > \@ClientDBName' + '.mdf' ,
> > SIZE =3D 11264KB ,
> > MAXSIZE =3D UNLIMITED,
> > FILEGROWTH =3D 1024KB )
> > LOG ON
> > ( NAME =3D N'@ClientDBName' + '_log',
> > FILENAME =3D N'C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\DATA
> > \@ClientDBName' + '_log.ldf' ,
> > SIZE =3D 1024KB ,
> > MAXSIZE =3D 2048GB ,
> > FILEGROWTH =3D 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'.- Hide quoted text -
>
> - Show quoted text -
Thank you very much. It works great... :)
Now I'll try to fight my way through building tables using the
variable name.
Re: Parameter for Stored Procedure
am 17.06.2007 15:21:10 von Joe Celko
>> 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.