two DDL statements and SELECT in single batch

Question

Post reply

two DDL statements and SELECT in single batch

ramoha

SSCommitted

Points: 1909
More actions
June 10, 2009 at 1:29 pm

#133153

Hi All,
I have to two DDL statements followed by a SELECT stmt which is ofcourse is not working 🙂
/*********************************
alter table foo add col2 int
create index idx on foo(col2)
select * from foo where col2 is null
**********************************/
Msg 207, Level 16, State 1, Line 5
Invalid column name 'col2'.
I can't use GO as batch separator because I have lots of variables declared that I'll be using again in the code.
Any workaround ideas greatly appreciated?
Regards,
Razi, M.
http://questivity.com/it-training.html

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Robert Klimes SSCoach Points: 17865 More actions · Answer 1

This works for me. Don't know if it still qualifies as single batch for you.

declare @sql nvarchar(max)

set @sql = '

alter table foo add col2 int;

create index idx on foo(col2);'

exec sp_executesql @sql;

set @sql = '

select * from foo where col2 is null ;'

exec sp_executesql @sql;

Bob
-----------------------------------------------------------------------------
How to post to get the best help[/url]

Glenn Dorling SSCertifiable Points: 7889 More actions · Answer 2

You need a semicolon or "go" statement between the different lines.

If some sort of statement delimiter is not between the statements SQL Server tries to resolve it as a single batch, and as the new field does not exist while it is trying to devise a query plan (and it's only a single query plan that is devised if there's only one batch) then the statement fails.

ramoha SSCommitted Points: 1909 More actions · Answer 3

Thank you very much:) I tried earlier in dynamic sql but the only mistake I made was that I didn't execute my select statment i.e the second stmt after ALTER n CREATE INDEX.

Wonderful:) Work 100%

Razi, M.
http://questivity.com/it-training.html