Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

Browse by Tags · View All
sql_server 217
t-sql 211
tsql 116
sqlserver 96
BRH 78
#SQLServer 66
#TSQL 56
SQL Server 34
function 11
SSMS 9

Archive · View All
August 2007 17
August 2010 8
June 2012 7
June 2011 7
November 2007 7
August 2012 6
May 2012 6
November 2011 6
August 2011 6
October 2011 6

Madhivanan's TSQL Blog

create/alter procedure in a single batch

Nov 2 2010 2:11AM by Madhivanan   

You know that Create or alter procedure statement should be always in a seperate batch otherwise you will get an error "'CREATE/ALTER PROCEDURE' must be the first statement in a query batch." Sometime when you want to create or alter the procedure based on the existance of a procedure you may wonder why the following is not working

if exists
(
	select * from INFORMATION_SCHEMA.ROUTINES where ROUTINE_NAME='testing'
)
alter procedure testing
(
	@i int
)
as
	select @i as number
else
create procedure testing
(
	@j int
)
as
	select @j as number


When you execute the above you get the error

Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'procedure'.
Msg 137, Level 15, State 2, Line 8
Must declare the scalar variable "@i".
Msg 111, Level 15, State 1, Line 15
'CREATE/ALTER PROCEDURE' must be the first statement in a query batch.

It is becuase CREATE or ALTER procedure statement should always be executed as seperate batch. Now if you want to simulate the above logic, only way is to make use of the dynamic sql as shown below

if exists
(
	select * from INFORMATION_SCHEMA.ROUTINES where ROUTINE_NAME='testing'
)
exec(
'alter procedure testing
(
	@i int
)
as
	select @i as number
')
else
exec(
'create procedure testing
(
	@j int
)
as
	select @j as number
')

Tags: t-sql, sql_server, procedure, sqlserver, tsql, BRH, #TSQL, #SQLServer, create/alter,


Madhivanan
3 · 40% · 12924
0
Liked
 
0
Lifesaver
 
0
Refreshed
 
0
Learned
 
0
Incorrect



Submit

4  Comments  

  • I believe the above will work fine if the block contains some straight forward statements inside the procedure. But if the procedure contains some very complex logic inlcuding writing dynamic queries, it will be hard to proceed in the above said way.

    commented on Nov 12 2010 12:27AM
    Amartha Dutta
    728 · 0% · 45
  • Yes it is difficult but managable. You need to make use of single quotes effectively if the procedure code has some dynamic sql

    commented on Nov 12 2010 2:08AM
    Madhivanan
    3 · 40% · 12924
  • Not much a fan of putting the code in a 'dynamic' block myself, I would go for * Check if procedure exists, if so then drop procedure; GO ; CREATE PROCEDURE... or * check if procedure exists, if not create dummy procedure; GO ; ALTER PROCEDURE

    The first version is what we always use here and works fine. I think it also is the way SQLMgmtStudio scripts things out. The second approach allows you to use ALTER if you'd prefer that for some reason.. (eg. if you don't want create date of the object to change)

    commented on Nov 16 2010 2:36PM
    Roby Van Hoye
    221 · 1% · 210
  • To add to Roby Van Hoye's comment, the ALTER is also required if you don't want the permissions on the object to change. If you drop and recreate objects, you need to reapply permissions everytime you do so.

    commented on Dec 27 2010 7:42AM
    Nakul Vachhrajani
    4 · 36% · 11635

Your Comment


Sign Up or Login to post a comment.

    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]