Use more than one sql statement in EXEC : EXEC « Transact SQL « SQL Server / T-SQL Tutorial

1. SQL Server / T-SQL Tutorial
2. Transact SQL
3. EXEC

5> DECLARE @schema AS VARCHAR(1000), @insert AS VARCHAR(1000);
6> SET @schema = 'col1 INT, col2 DECIMAL(10, 2)';
7> SET @insert = 'INSERT INTO #T42 VALUES(10, 20.30)';
8>
10> CREATE TABLE #T42(dummycol INT);
11>
17> EXEC('
18~ ALTER TABLE #T42 ADD ' + @schema + ';
19~ ALTER TABLE #T42 DROP COLUMN dummycol;
20~
21~ EXEC(''' + @insert + ''')');
22> GO
1>
2> -- Back in the outer level, access #T in a new batch
3> SELECT * FROM #T42;
4> GO
col1        col2
----------- ------------
         10        20.30
1> -- Cleanup
2> DROP TABLE #T42;
3> GO