Wednesday, January 23, 2013

SET NOEXEC ON : avoid invalid object error

When SET NOEXEC is ON, SQL Server compiles Transact-SQL statements but does not execute them. If one uses this SET command in the middle of SQL scripts, the SQL statements are not executed until it meets SET NOEXEC OFF.
So this SET NOEXEC is pretty convenient when we want to block some portion of the codes, but there is some limitation. Since SET NOEXEC compiles TSQL statements, if there is any compilation error, it will raise an error. For example, if there is invalid object such as invalid table name or invalid database name in the script, error will be raised. Sometimes this is not the desired result.
Say, we might want to use [USE db] statement even when the DB is not already created since the script will not be executed anyway. Take the following example. If TEST db does not exist, the script will raise an error and depending on the application running this script, the error might become an exception and no further statements will be executed.
-- cut above --
SET NOEXEC ON

USE [TEST]
IF NOT EXISTS(SELECT * FROM sys.database_principals WHERE name = 'Tester')
BEGIN
 CREATE USER [Tester] FOR LOGIN [Tester]
 EXEC sp_addrolemember N'db_datareader', N'Tester'
 EXEC sp_addrolemember N'db_datawriter', N'Tester'
END
GO

SET NOEXEC OFF
USE master
GO
SELECT * FROM sys.data_spaces;
-- cut below --
So the solution? I put the the statement that might cause an error into EXEC(). This way we can postpone any error until we actually execute them. So there will be no compilation error and the script will run even if there is no TEST db.
-- cut above --
SET NOEXEC ON

EXEC ('USE [TEST];
IF NOT EXISTS(SELECT * FROM sys.database_principals WHERE name = ''Tester'')
BEGIN
 CREATE USER [Tester] FOR LOGIN [Tester]
 EXEC sp_addrolemember N''db_datareader'', N''Tester''
 EXEC sp_addrolemember N''db_datawriter'', N''Tester'' 
END')
GO

SET NOEXEC OFF
USE master
GO
SELECT * FROM sys.data_spaces;
-- cut below --

No comments:

Post a Comment