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 --