Variables in SQL-script SQL2005

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • =?ISO-8859-1?Q?Brian_Sprog=F8?=
    #1

    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_DATABAS E=[master], DEFAULT_LANGUAG E=[us_english],
    CHECK_EXPIRATIO N=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
    Tags: None
    • Dan Guzman
      #2
      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(@brug ernavn) +
      N' WITH PASSWORD = ' +
      QUOTENAME(@pass word, '''');

      EXECUTE (@SqlStatement) ;

      --
      Hope this helps.

      Dan Guzman
      SQL Server MVP

      "Brian Sprogø" <brs@mailme.inv alidwrote in message
      news:479086ad$0 $15886$edfadb0f @dtext01.news.t ele.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_DATABAS E=[master], DEFAULT_LANGUAG E=[us_english],
      CHECK_EXPIRATIO N=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

        Comment

        Previous template Next
        Working...