asked on

Statement separators in Dynamic SQL in SQL Server

Hello,
I have a situation where I need to issue a statement separator (like go) in dynamic sql.

Example:
Declare @sql varchar(1000)
select @sql = "Drop Proc testproc go Create Proc testproc"
Execute (@sql)

Please let me know how to do this in SQL Server (2000/2005).

Thanks,
Harish

chapmandew

does your statement not execute?

Harish Varghese

ASKER

Hi chapmandew,
I get below error when I execute the above example:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'go'.
Server: Msg 111, Level 15, State 1, Line 1
'CREATE PROCEDURE' must be the first statement in a query batch.

ASKER CERTIFIED SOLUTION

chapmandew

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Harish Varghese

ASKER

Ok.
I believe statement separators are handled by the query tool (Query Analyzer, SQL Server Management Studio etc).
I executed the below batch in Query Analyzer.
Drop Proc testproc
go
Create Proc testproc as select 1

I saw two batches 'Drop Proc testproc' and 'Create Proc testproc as select 1' in Profiler.
So, I believe, it may not be possible execute two sql batches together.
Thanks,
Harish

chapmandew

you are correct, sir.

Harish Varghese

ASKER

Hi,
Though I did not get a solution for my problem, I am giving you points because I think there is no solution to this.
Thanks,
Harish