Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Statement separators in Dynamic SQL in SQL Server

Posted on 2008-10-02
6
Medium Priority
?
537 Views
Last Modified: 2012-08-14
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
0
Comment
Question by:Harish Varghese
[X]
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
6 Comments
 
LVL 60

Expert Comment

by:chapmandew
ID: 22626443
does your statement not execute?
0
 
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.
0
 
LVL 60

Accepted Solution

by:
chapmandew earned 1500 total points
ID: 22626798
yep.  Looks like you can't do it....you're going to need to split it into 2 different statements.
0
Independent Software Vendors: 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!

 
LVL 12

Author Comment

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

Expert Comment

by:chapmandew
ID: 22626883
you are correct, sir.
0
 
LVL 12

Author Closing Comment

by:Harish Varghese
ID: 31502468
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
0

Featured Post

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

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

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

715 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