Re: Variables in SQL-script SQL2005

#1

Quote:

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ø" <brs (AT) mailme (DOT) invalid> wrote

Quote:

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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Re: Variables in SQL-script SQL2005

comp.databases.ms-sqlserver comp.databases.ms-sqlserver

Re: Variables in SQL-script SQL2005 - 01-18-2008 , 07:51 AM