Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 544
  • Last Modified:

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
0
Harish Varghese
Asked:
Harish Varghese
  • 3
  • 3
1 Solution
 
chapmandewCommented:
does your statement not execute?
0
 
Harish VargheseProject LeaderAuthor Commented:
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
 
chapmandewCommented:
yep.  Looks like you can't do it....you're going to need to split it into 2 different statements.
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
Harish VargheseProject LeaderAuthor Commented:
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
 
chapmandewCommented:
you are correct, sir.
0
 
Harish VargheseProject LeaderAuthor Commented:
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now