sp_execute for multiple dynamic T-SQL statements ak a batch

Question

Ideally I'd like to execute several sql statements as a single exec sp_executesql statement. An example would be where I use one IF EXISTS to determine if a second statement is run:

drop proc ai_ImportDataAddListPosn
go

create proc ai_ImportDataAddListPosn(@ParamTableName NVARCHAR(255), @debug INT ) 
AS
BEGIN
DECLARE @sql AS NVARCHAR(4000)
SET @sql = N'IF NOT EXISTS(SELECT * FROM syscolumns INNER JOIN sysobjects ON syscolumns.id = sysobjects.id WHERE sysobjects.name = ''' + @ParamTableName + ''' AND Syscolumns.name = ''ListPosn'');'
            + 'alter table [' + @ParamTableName + '] add ListPosn int identity(1,1)'
IF @debug = 1 PRINT @sql
EXEC sp_executesql @sql
END
Go

EXEC ai_ImportDataAddListPosn DeptForMove, 1

I realise that this example does not test for the existence of the table first, its just a simplified example not the real problem. I also am aware of SQL injection and how to combat it. I'm reasonably happy that both statements are good SQL

I thought the ";" may act as a statement terminator

Answer 1

I also am aware of SQL injection and how to combat it. I'm reasonably happy that both statements are good SQL

There's scant evidence of that in the question. You should be using parameterisation and QUOTENAME.

I thought the ";" may act as a statement terminator

It does but you don't want a statement terminator there.

IF 1=1; SELECT 'Foo';

is invalid syntax.

IF 1=1 SELECT 'Foo';

would work fine however. You just need to replace the semicolon after your Boolean_expression with white space.