Variables in SQL-script SQL2005

Hi,

I seem to have missed something here...

Made a script for creating users on a SQL2005 - and it works just fine.

To make things easier I would like to add a couple of variables to get
around typing e.g. the username 3 times.

My script looks like this:


USE [master]
GO
DECLARE @brugernavn as char(50)
DECLARE @password as char(50)

set @brugernavn = 'testuser'
set @password = N'testpassword'

select @brugernavn
select @password

CREATE LOGIN @brugernavn WITH PASSWORD= @password,
DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english],
CHECK_EXPIRATION=ON, CHECK_POLICY=ON

USE [testbase]

CREATE USER @brugernavn FOR LOGIN @brugernavn
GO

The 2 select-statemens is for verifying the contents of the variables.

The script fails at the line 'CREATE LOGIN...' with the following messages:

Msg 102, Level 15, State 1, Line 10
Incorrect syntax near '@brugernavn'.
Msg 319, Level 15, State 1, Line 10
Incorrect syntax near the keyword 'with'. If this statement is a common
table expression or an xmlnamespaces clause, the previous statement must
be terminated with a semicolon.
Msg 102, Level 15, State 1, Line 14
Incorrect syntax near '@brugernavn'.

How do I get the script to do what I want it to ?

Best regards,

Brian

Re: Variables in SQL-script SQL2005

> How do I get the script to do what I want it to ?

Not all DCL commands accept variables (see BOL syntax) so you'll need to use
dynamic SQL. For example:

DECLARE @SqlStatement nvarchar(MAX);

SET @SqlStatement =
N'CREATE LOGIN ' +
QUOTENAME(@brugernavn) +
N' WITH PASSWORD = ' +
QUOTENAME(@password, '''');

EXECUTE (@SqlStatement);

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Brian Sprogø" wrote in message
news:479086ad$0$15886$edfadb0f@dtext01.news.tele.dk...
> Hi,
>
> I seem to have missed something here...
>
> Made a script for creating users on a SQL2005 - and it works just fine.
>
> To make things easier I would like to add a couple of variables to get
> around typing e.g. the username 3 times.
>
> My script looks like this:
>
>
> USE [master]
> GO
> DECLARE @brugernavn as char(50)
> DECLARE @password as char(50)
>
> set @brugernavn = 'testuser'
> set @password = N'testpassword'
>
> select @brugernavn
> select @password
>
> CREATE LOGIN @brugernavn WITH PASSWORD= @password,
> DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english],
> CHECK_EXPIRATION=ON, CHECK_POLICY=ON
>
> USE [testbase]
>
> CREATE USER @brugernavn FOR LOGIN @brugernavn
> GO
>
> The 2 select-statemens is for verifying the contents of the variables.
>
> The script fails at the line 'CREATE LOGIN...' with the following
> messages:
>
> Msg 102, Level 15, State 1, Line 10
> Incorrect syntax near '@brugernavn'.
> Msg 319, Level 15, State 1, Line 10
> Incorrect syntax near the keyword 'with'. If this statement is a common
> table expression or an xmlnamespaces clause, the previous statement must
> be terminated with a semicolon.
> Msg 102, Level 15, State 1, Line 14
> Incorrect syntax near '@brugernavn'.
>
> How do I get the script to do what I want it to ?
>
> Best regards,
>
> Brian

Re: Variables in SQL-script SQL2005

Hi Dan,

I modified the script to look like this, and it worked like a charm :-)

USE [master]
GO
DECLARE @brugernavn as char(50)
DECLARE @password as char(50)
DECLARE @SqlStatement nvarchar(MAX);

set @brugernavn = 'testbruger'
set @password = N'testpassword'

SET @SqlStatement =
N'CREATE LOGIN ' +
QUOTENAME(@brugernavn) +
N' WITH PASSWORD = ' +
QUOTENAME(@password, '''') +
N', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english],
CHECK_EXPIRATION=ON, CHECK_POLICY=ON ';

EXECUTE (@SqlStatement);

use [testbase]

SET @SqlStatement =
N'CREATE USER ' +
QUOTENAME(@brugernavn) +
N' FOR LOGIN ' +
QUOTENAME(@brugernavn)

EXECUTE (@SqlStatement);
GO

Thanks a lot for your help.

Best regards,

Brian

Dan Guzman skrev:
>> How do I get the script to do what I want it to ?
>
> Not all DCL commands accept variables (see BOL syntax) so you'll need to
> use dynamic SQL. For example:
>
> DECLARE @SqlStatement nvarchar(MAX);
>
> SET @SqlStatement =
> N'CREATE LOGIN ' +
> QUOTENAME(@brugernavn) +
> N' WITH PASSWORD = ' +
> QUOTENAME(@password, '''');
>
> EXECUTE (@SqlStatement);
>