I have faced this problem many times and have developed a very reliable and consistent way deal with it. It all has to do with the fact that many SQL DDL commands have requirements about batch begins and ends, and that you do not have GO available to you in stored procedures ("GO" is not a T-SQL command, it's actually a Management Studio command).

the general solution is to realize that Dynamic SQL executions each constitute their own separate batch. Here is a quick & easy demonstration of that:

Select * from information_schema.tables

exec('

Select * from information_schema.tables

USE [tempdb]

Select * from information_schema.tables

USE [master]

Select * from information_schema.tables

')

Select * from information_schema.tables

Now try it in a stored procedure too, it still works, just as well. Here is an outline of my technique, with problems and solutions:

---

solution: 1) Use Stored Procedures.

problem: "USE" is ineffective in a Stored Proc.

solution: 2) Use Dynamic SQL in your stored procs.

Now I'll anticipate some of the problems that you'll run into down the road:

problem: Many DDL statements need to be in their own batch, and I still don't have "GO".

solution: 3) First use Dynamic SQL to execute the USE, then "nest" a second level of dynamic SQL batches within that one to execute separate DDL commands.

problem: When something goes wrong, it's hard to figure out what was executing.

solution: 4) When using dynamic SQL, always print out the string before executing it.

problem: The errors or the error messages seem to be disappearing.

solution: 5) Always use Try/Catch in the dynamic SQL and display the error from the Catch statement.

problem: How do the apostrophes work when your doubly-nested?

solution: 6) You double the apostrophes every extra level in, when using dynamic SQL (so try to keep it to two levels).

problem: I am not allowed to use Dynamic SQL because of SOX/Corp Guidelines/Other Auditors.

solution: 7) Re-Educate them. Dynamic SQL is as safe as any other form of SQL as long as you do not start executing user input strings. Sometime's it is the best, or even the *only* solution (like here). They need to understand that.

problem: This all seems like a lot and/or confusing. Do you have an example?

solution: Sure! Here's an example command procedure that can execute any single command in another database that follows all of these rules:

CREATE proc spDo_NoErr(@Command nvarchar(max), @DB nvarchar(80) = '')

as

/*

Procedure to execute a dynamic string, suppressing any errors.

Errors will be printed, along with the command, but will not be

raised back to the caller.

21-may-2008RBarryYoung:Created.

*/

Declare @SQL nvarchar(max)

--Setup the database string:

IF @DB != ''

BEGIN

Select @DB = 'USE ' + @DB + '; --switch to the DB before using DDL statements

'

END

--double quotes in the original @sql string:

Select @Command = Replace(@Command, '''', '''''')

--build the final string to execute:

Select @SQL = @DB + 'BEGIN TRY

EXEC(N'''+ @Command +''')

End Try

Begin Catch

Print ''Ignoring %ERROR: '' + Error_Message()

Print ''While executing: "' + @Command + '"''

End Catch

'--wrap in another EXEC because DDL statements must be first in a batch

PRINT 'Executing: ' + @SQL

--Do it.

EXEC dbo.sp_executesql @SQL

IF @@ERROR<>0

Print @SQL--Show the command, if it failed.

I have much larger examples too. Some that create whole databases with this technique. Let me know and I can provide you with a copy of one.