Must declare the scalar variable "@varname" in database creation script

Question

I'm trying to create a script for basic database and login/user creation for SqlServer When i run my script I get many errors like:

Must declare the scalar variable "@varname"

when the variable is used in an EXEC statement for the second time during the script execution.

declare
     @dbname nvarchar(50) ,
     @userName nvarchar(50) ,
     @password nvarchar(50);
SET @dbname = N'DBNAME';
SET @userName = N'DBUSERNAME';
SET @password= N'PASSWORD';

Begin transaction
    IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = @dbname)
    BEGIN
        exec ('
            DROP login '+ @userName + '
            DROP USER '+  @userName + '
            alter database '+  @dbname + '
            SET OFFLINE 
            WITH ROLLBACK IMMEDIATE;
            ALTER DATABASE '+  @dbname + '
            SET ONLINE;
            DROP DATABASE '+  @dbname)
    END 

    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_PADDING ON
    GO
    EXEC ('CREATE DATABASE '+ @dbname)
    GO 
    EXEC (@dbname+ '.[dbo].[sp_fulltext_database] @action = ''disable''')
    GO 
    EXEC ('ALTER DATABASE '+ @dbname + ' SET COMPATIBILITY_LEVEL = 100')
    GO 
     ...
    EXEC ('ALTER DATABASE '+ @dbname + ' SET DB_CHAINING OFF')
    GO

    EXEC('create login '+ @userName + 'with password = '+ @password);
    EXEC('use  '+ @dbname+ '
    create user '+ @userName + ' for login '+ @userName);
    EXEC ('EXEC sp_addrolemember  ''db_owner'', '+  @userName)

commit transaction

Have I to use sp_executesql for every query? Is there a shorter solution?

Answer 1

The GO Statement tells the query analyzer that a batch is complete.

http://technet.microsoft.com/en-us/library/ms188037.aspx

Therefore, the declared variable that are set are out of scope by the time the dynamic code is executed.

If you truly want this in a transaction, then wrap it with BEGIN TRY/END TRY in the BEGIN CATCH /END CATCH, perform a ROLLBACK.

http://craftydba.com/?p=5930

I never tried this with the CREATE DATABASE statement. That might be a fun exercise. Does it undo the database creation? Something to add to my bucket list to try.

Also, you need to use a semicolon (;) when combining multiple commands. Otherwise, you will get a syntax error.

Answer 2

Thanks to Raj and Vaibhav Parmar comments I changed my script and now it works fine.

The main error was the GO statement that should be removed because it is used to group SQL commands into batches which are sent to the server together. The commands included in the batch, that is, the set of commands since the last GO command or the start of the session, must be logically consistent. You can't define a variable in one batch and then use it in another since the scope of the variable is limited to the batch in which it's defined.

The second error was that I tried to use transaction with statement that don't allow them

I leave the corrected script there as reference:

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
SET ANSI_PADDING ON

declare
     @dbname nvarchar(50) ,
     @userName nvarchar(50) ,
     @password nvarchar(50);
SET @dbname = N'DATABASENAME';
SET @userName = N'DBUSERNAME';
SET @password= N'DBUSERPASSWORD';

IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = @dbname)
BEGIN
    exec ('
    DROP USER '+  @userName + '
    DROP login '+ @userName + '
    alter database '+  @dbname + '
    SET OFFLINE 
    WITH ROLLBACK IMMEDIATE;
    ALTER DATABASE '+  @dbname + '
    SET ONLINE;
    DROP DATABASE '+  @dbname)
END 

EXEC ('CREATE DATABASE '+ @dbname);
EXEC (@dbname+ '.[dbo].[sp_fulltext_database] @action = ''disable''');
EXEC ('ALTER DATABASE '+ @dbname + ' SET COMPATIBILITY_LEVEL = 100');
EXEC ('ALTER DATABASE '+ @dbname + ' SET ANSI_NULL_DEFAULT OFF');
EXEC ('ALTER DATABASE '+ @dbname + ' SET ANSI_NULLS ON');
EXEC ('ALTER DATABASE '+ @dbname + ' SET ANSI_PADDING ON');
EXEC ('ALTER DATABASE '+ @dbname + ' SET AUTO_CLOSE OFF');
EXEC ('ALTER DATABASE '+ @dbname + ' SET AUTO_SHRINK OFF');
EXEC ('ALTER DATABASE '+ @dbname + ' SET QUOTED_IDENTIFIER ON');
EXEC ('ALTER DATABASE '+ @dbname + ' SET RECOVERY FULL');
EXEC ('ALTER DATABASE '+ @dbname + ' SET PAGE_VERIFY CHECKSUM');
EXEC ('ALTER DATABASE '+ @dbname + ' SET ANSI_WARNINGS ON');
EXEC ('ALTER DATABASE '+ @dbname + ' SET ARITHABORT ON');
EXEC ('ALTER DATABASE '+ @dbname + ' SET AUTO_CREATE_STATISTICS ON');
EXEC ('ALTER DATABASE '+ @dbname + ' SET AUTO_UPDATE_STATISTICS ON');
EXEC ('ALTER DATABASE '+ @dbname + ' SET CURSOR_CLOSE_ON_COMMIT OFF');
EXEC ('ALTER DATABASE '+ @dbname + ' SET CURSOR_DEFAULT  GLOBAL');
EXEC ('ALTER DATABASE '+ @dbname + ' SET CONCAT_NULL_YIELDS_NULL OFF'); 
EXEC ('ALTER DATABASE '+ @dbname + ' SET NUMERIC_ROUNDABORT OFF'); 
EXEC ('ALTER DATABASE '+ @dbname + ' SET RECURSIVE_TRIGGERS OFF'); 
EXEC ('ALTER DATABASE '+ @dbname + ' SET  ENABLE_BROKER'); 
EXEC ('ALTER DATABASE '+ @dbname + ' SET AUTO_UPDATE_STATISTICS_ASYNC OFF'); 
EXEC ('ALTER DATABASE '+ @dbname + ' SET DATE_CORRELATION_OPTIMIZATION OFF'); 
EXEC ('ALTER DATABASE '+ @dbname + ' SET TRUSTWORTHY OFF'); 
EXEC ('ALTER DATABASE '+ @dbname + ' SET ALLOW_SNAPSHOT_ISOLATION OFF'); 
EXEC ('ALTER DATABASE '+ @dbname + ' SET PARAMETERIZATION SIMPLE'); 
EXEC ('ALTER DATABASE '+ @dbname + ' SET READ_COMMITTED_SNAPSHOT OFF'); 
EXEC ('ALTER DATABASE '+ @dbname + ' SET HONOR_BROKER_PRIORITY OFF'); 
EXEC ('ALTER DATABASE '+ @dbname + ' SET  READ_WRITE'); 
EXEC ('ALTER DATABASE '+ @dbname + ' SET  MULTI_USER'); 
EXEC ('ALTER DATABASE '+ @dbname + ' SET DB_CHAINING OFF');

EXEC ('create login '+  @userName + ' with password = '''+ @password+ ''', default_database = ' + @dbname);
EXEC ('use  '+ @dbname+ ' create user '+ @userName + ' for login '+ @userName);
EXEC ('use  '+ @dbname+ ' EXEC sp_addrolemember  ''db_owner'', '+  @userName);

Answer 3

Just use below code might be it helps:

declare @dbname nvarchar(50) ,@userName nvarchar(50) ,@password nvarchar(50);
SET @dbname = N'DBNAME';
SET @userName = N'DBUSERNAME';
SET @password= N'PASSWORD';

Begin transaction
IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = @dbname)
BEGIN
    exec ('
        DROP login '+ @userName + '
        DROP USER '+  @userName + '
        alter database '+  @dbname + '
        SET OFFLINE 
        WITH ROLLBACK IMMEDIATE;
        ALTER DATABASE '+  @dbname + '
        SET ONLINE;
        DROP DATABASE '+  @dbname)
END 
   else
   begin
--SET ANSI_NULLS ON
--GO
SET QUOTED_IDENTIFIER ON
--GO
--SET ANSI_PADDING ON
--GO
EXEC ('CREATE DATABASE '+ @dbname)
--GO 
EXEC (@dbname+ '.[dbo].[sp_fulltext_database] @action = ''disable''')
--GO 
EXEC ('ALTER DATABASE '+ @dbname + ' SET COMPATIBILITY_LEVEL = 100')
--GO 

EXEC ('ALTER DATABASE '+ @dbname + ' SET DB_CHAINING OFF')
--GO

EXEC('create login '+ @userName + 'with password = '+ @password);
EXEC('use  '+ @dbname+ '
create user '+ @userName + ' for login '+ @userName);
EXEC ('EXEC sp_addrolemember  ''db_owner'', '+  @userName)
end

commit transaction