I’ve grown up reading Tom Clancy and probably most of you have at least seen Red October, so this book caught my eye when browsing used books for a recent trip. It’s a fairly human look at what’s involved in sailing on a Trident missile submarine…
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.
What happens if I execute this procedure? I’ll see something like this:
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.
Once I do that, a new query window opens. This is the code in there.
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.