Statement separators in Dynamic SQL in SQL Server

Posted on 2008-10-02
Last Modified: 2012-08-14
I have a situation where I need to issue a statement separator (like go) in dynamic sql.

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).

Question by:Harish Varghese
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
LVL 60

Expert Comment

ID: 22626443
does your statement not execute?
LVL 12

Author Comment

by:Harish Varghese
ID: 22626776
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.
LVL 60

Accepted Solution

chapmandew earned 500 total points
ID: 22626798
yep.  Looks like you can't do're going to need to split it into 2 different statements.
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

LVL 12

Author Comment

by:Harish Varghese
ID: 22626878
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
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.
LVL 60

Expert Comment

ID: 22626883
you are correct, sir.
LVL 12

Author Closing Comment

by:Harish Varghese
ID: 31502468
Though I did not get a solution for my problem, I am giving you points because I think there is no solution to this.

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Sql query 107 87
ISDATE() not working properly on my table? Any suggestions. 7 46
SQL query with cast 38 59
tempdb log keep growing 7 44
This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question