0

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

Hadi
  • 36,233
  • 13
  • 65
  • 124
user2711068
  • 3
  • 1
  • 6

1 Answers1

0

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.

Martin Smith
  • 438,706
  • 87
  • 741
  • 845
  • I thought the fact that I had a debug print indicated that I had an output and the ability to run the generated sql (which I did). I split the statements and they both parsed. Thank you for pointing out the invalid syntax. Actually I came to the parametrisation conclusion myself but gave up thinking there might be an actual response that was worthwhile after the fourth irrelevant errata edit. Your response stands above the muddy crowd of OCD editors. – user2711068 Nov 07 '16 at 09:18