Executing SQL Server Batch Statements Within One Exec(sql) Call

When you need to GO in your batch

Sometimes you'll run into a situation where you want to script a series of SQL batch statements dynamically.  For instance, you might want to create a schema, then create a user to use that schema, then populate that schema with tables, and do that in each database that runs on a SQL instance.


If you weren't wanting to do this dynamically, you'd simply do something like this:

use [dbname];
go
Create schema [schema_name];
go
create user [username] for login [loginname] with default_schema=[schema_name];
go

That's not that big of a deal, really.  But if we want to create a cursor to loop through all databases and run this SQL dynamically, we have a problem:  we can't have the create schema statement in the same batch as the USE command, so we don't end up creating the schema in the correct database.



This is because we can't use a GO command in an exec call:  GO isn't valid SQL; it's a batch separator that SSMS (or sqlcmd, or osql) interprets.  So if we put a GO command in our string to be executed, it'll fail.

It turns out, however, we can nest EXEC commands, to create batches within batches.  Instead of using the cursor for our first example, we'll simplify things, just putting all of this in one string to execute at the end:

Declare @sqlstm varchar(1800)
Declare @dbname sysname
set @sqlstm='
use [dbname];
exec(''Create schema [schema_name];'');
exec(''create user [username] for login [loginname] with default_schema=[schema_name];'');'

exec (@sqlstm)
go

Just as a little example, you can see exactly how things work with this:

Declare @sqlstm varchar(1800)
set @sqlstm='
use [testdb];
exec(''Create schema [test_schema];'');
exec(''Create schema [test_schema2];'');
exec(''Create schema [test_schema3];'');
exec(''select name from sys.schemas;'');
exec(''drop schema [test_schema3];'');
exec(''select name from sys.schemas;'');'

exec (@sqlstm)

So:  multiple batches in a single dynamic SQL call.  Enjoy!