none
Must declare the scalar variable "@LOGINDB ??? RRS feed

  • Question

  • Can any one help me on this... Variables are declared but It gives me an error message --

    Must declare the scalar variable "@LOGINDB

    Must declare the scalar variable "@LOGINLANG".

     

    DB - SQL Server 2005

     

    Script ----------------------------------------------------------------------------------------------------------------------------------------

     

    declare @LOGINDB varchar(132)

    declare @LOGINLANG varchar(132)

    Set @LOGINDB = N'master'

    Set @LOGINLANG = N'us_english'

    EXEC('If not exists (select * from master.dbo.syslogins where loginname = [' + N'ABC' + '])

    BEGIN

    if [' + @LOGINDB + '] is null or not exists (select * from master.dbo.sysdatabases where name = ['+ @LOGINDB +'])

    set @LOGINDB = + [' + N'master' + ']

    if ['+ @LOGINLANG + '] is null or (not exists (select * from master.dbo.syslanguages where name = ['+ @LOGINLANG +']) and [' + @LOGINLANG + ']<> [' + N'us_english' + '])

    set @LOGINLANG = @@language

    CREATE LOGIN [DIARH] WITH PASSWORD = '''', DEFAULT_DATABASE = [' + @LOGINDB + '], DEFAULT_LANGUAGE= [' + @LOGINLANG + '], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

    END')

     

    ----------------------------------------------------------------------------------------------------------------------------------------

     

     

    Msg 137, Level 15, State 1, Line 4

    Must declare the scalar variable "@LOGINDB".

    Msg 137, Level 15, State 1, Line 7

    Must declare the scalar variable "@LOGINLANG".

    Wednesday, September 12, 2007 2:38 PM

All replies

  • Within the script you are executing a subscript of concatenated SQL.  The SQL statement is executed independent of the stored procedure you  defined so it can't see the declared variables unless you also declare them within the EXEC Sql Script.

    My recommendation is to undo the concatenation.

    declare @LOGINDB varchar(132)
    declare @LOGINLANG varchar(132)

    Set @LOGINDB = N'master'
    Set @LOGINLANG = N'us_english'

    If not exists (select * from master.dbo.syslogins where loginname = 'ABC')

    BEGIN

    if @LOGINDB is null or not exists (select * from master.dbo.sysdatabases where name = [@LOGINDB)

    set @LOGINDB = N'master'

    if @LOGINLANG is null or (not exists (select * from master.dbo.syslanguages where name = @LOGINLANG) and @LOGINLANG<> N'us_english')

    set @LOGINLANG = @@language

    CREATE LOGIN [DIARH] WITH PASSWORD = '''', DEFAULT_DATABASE = @LOGINDB, DEFAULT_LANGUAGE= @LOGINLANG, CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

    END


    otherwise you would have to add

    declare @LOGINDB varchar(132)

    declare @LOGINLANG varchar(132)


    to the Exec SQL


    EXEC (

    'declare @LOGINDB varchar(132)

    declare @LOGINLANG varchar(132)

    If not exists (select * f
    ..............






    Wednesday, September 12, 2007 2:50 PM