SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Be Careful of Your Create Stored Procedure Batch

I was rehearsing a demo with someone recently and we had some stored procedure code that looked like this:

CREATE PROCEDURE UpdateEmpID @empid INT
 AS
 BEGIN
 UPDATE  dbo.Employees
 SET empid = 3
 WHERE  empid = @empid
 ;
 END

However, this was part of a batch that had all of this code (proc code repeated).

CREATE PROCEDURE UpdateEmpID @empid INT
 AS
 BEGIN
 UPDATE  dbo.Employees
 SET empid = 3
 WHERE  empid = @empid
 ;
 END

-- test the procedure execution
 -- exec UpdateEmpID 2

SELECT empid
 FROM dbo.Employees
 WHERE empid = 3

When I execute this, I see a simple message. If I’m not paying attention, this seems to make sense.

2016-12-15 16_02_48-SQLQuery2.sql - WAY0UTWESTVAIO_SQL2016.sandbox (WAY0UTWESTVAIO_way0u (54))_ - Mi

What happens if I execute this procedure? I’ll see something like this:

2016-12-15 16_04_24-SQLQuery2.sql - WAY0UTWESTVAIO_SQL2016.sandbox (WAY0UTWESTVAIO_way0u (54))_ - Mi

At first glance, you’d think this makes sense. However, what has happened here? The procedure executed, which has an update, and I have a result set at the end. If I look at the proc code, this makes more sense. I’ll right click the procedure and select modify.

2016-12-15 16_05_37-

Once I do that, a new query window opens. This is the code in there.

2016-12-15 16_07_24-SQLQuery5.sql - WAY0UTWESTVAIO_SQL2016.sandbox (WAY0UTWESTVAIO_way0u (62)) - Mic

Why is my select code in there? That was designed to be a piece of test code. Shouldn’t the BEGIN..END after the AS define my procedure?

Actually it doesn’t. the procedure doesn’t end until the CREATE PROCEDURE statement is terminated. That termination comes by ending the batch. The CREATE PROCEDURE documentation has this limitation:

The CREATE PROCEDURE statement cannot be combined with other Transact-SQL statements in a single batch.

This means that anything else you have in that batch will be considered as part of the procedure, regardless of BEGIN..END.

I hadn’t noticed, or seen this before. Perhaps because I’m in the habit of including a GO between all my code, it hasn’t been an issue.

I would hope most people would catch this before any code is deployed with testing, but perhaps not Be aware that stored procedures should be compiled in their own batches, always.


Filed under: Blog Tagged: syndicated, T-SQL

The Voice of the DBA

Steve Jones is the editor of SQLServerCentral.com and visits a wide variety of data related topics in his daily editorial. Steve has spent years working as a DBA and general purpose Windows administrator, primarily working with SQL Server since it was ported from Sybase in 1990. You can follow Steve on Twitter at twitter.com/way0utwest

Comments

Leave a comment on the original post [voiceofthedba.com, opens in a new window]

Loading comments...