Be Careful of Your Create Stored Procedure Batch

Steve Jones, 2017-01-03

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

Rate

Share

Share

Rate

Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.

Robert Davis

2009-02-23

1,567 reads

Networking – Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I’d like to talk about social networking. We’ll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let…

Andy Warren

2009-02-17

1,530 reads

Speaking at Community Events – More Thoughts

Last week I posted Speaking at Community Events – Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I’ve got a few more thoughts on the topic this week, and I look forward to your comments.

Andy Warren

2009-02-13

360 reads