Delivery timelines and working till late in the evening – we have all been through these situations. This post is a true story – a confession about an incident that happened with me a couple of days ago. I was busy preparing a deployment script with a large number of stored procedures when I was distracted by a phone call reasonably late in the day. When I returned back to work, I made an error in the script that I was writing. What resulted afterwards was that the late evening turned into late night at work. As strange as it may seem, but when the error was caught, I simply laughed out loud at myself.
Test Scenario
Shown below is a quick demo of the mistake that I made. The script below creates two stored procedures – dbo.proc_Add2Numbers and dbo.proc_Multiply2Numbers.
But there is something wrong. Once you have gone through the script, pause a while and see if you can figure out the error.
USE tempdb;
GO
–Create the test procedures
IF OBJECT_ID(‘dbo.proc_Add2Numbers’,’P’) IS NOT NULL
DROP PROCEDURE dbo.proc_Add2Numbers;
GO
CREATE PROCEDURE dbo.proc_Add2Numbers
@iA INT,
@iB INT
AS
BEGIN
SET NOCOUNT ON;SELECT (@iA + @iB) AS SumAB;
END;IF OBJECT_ID(‘dbo.proc_Multiply2Numbers’,’P’) IS NOT NULL
DROP PROCEDURE dbo.proc_Multiply2Numbers;
GO
CREATE PROCEDURE dbo.proc_Multiply2Numbers
@iA INT,
@iB INT
AS
BEGIN
SET NOCOUNT ON;SELECT (@iA * @iB) AS MultiplyAB;
END;
GO
The Result
Let’s run the test that make my head spin that evening. What happened was that a test similar to the following ran fine for the first time:
USE tempdb;
GO
EXEC dbo.proc_Multiply2Numbers @iA = 2, @iB = 5;
GO
EXEC dbo.proc_Add2Numbers @iA = 2, @iB = 5;
GO
But, when I tried to run it again with a different set of parameters:
USE tempdb;
GO
EXEC dbo.proc_Multiply2Numbers @iA = 3, @iB = 6;
GO
EXEC dbo.proc_Add2Numbers @iA = 3, @iB = 6;
GO
I landed with the following error:
Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure ‘dbo.proc_Multiply2Numbers’.
So, I returned the database to it’s base state and repeated the process again – same error! It had already been way beyond my normal work hours and this error did it’s part to keep in the office for an hour more!
The Root Cause
If you have already figured out the error, that’s really great – you will surely have a great day ahead! But, I was not so lucky. After an hour of scratching my head, drinking coffee and looking at the script over and over again, I finally realized by mistake:
A missing batch terminator – “GO”!
If you, like me were unable did not figure it out, look at the script again. Or better still, run the following:
USE tempdb;
GO
SELECT OBJECT_DEFINITION(OBJECT_ID(‘dbo.proc_Add2Numbers’,’P’));
GO/***********/
/* Results */
/***********/
CREATE PROCEDURE dbo.proc_Add2Numbers
@iA INT,
@iB INT
AS
BEGIN
SET NOCOUNT ON;SELECT (@iA + @iB) AS SumAB;
END;–IMPORTANT: Notice the missing GO here!
IF OBJECT_ID(‘dbo.proc_Multiply2Numbers’,’P’) IS NOT NULL
DROP PROCEDURE dbo.proc_Multiply2Numbers;
As you can see from the script results above, the batch terminator “GO” was missing between the two stored procedures in the deployment script because of which the script to check for the existence of dbo.proc_Multiple2Numbers was included in the definition of dbo.proc_Add2Numbers.
When I ran the test for the first time, I executed dbo.proc_Multiply2Numbers first. When the dbo.proc_Add2Numbers was executed, it dropped the procedure dbo.proc_Multiply2Numbers which is why it was unavailable in round #2.
Lessons Reminded/Learnt
I realized two lessons on that day:
- Reminder: A stored procedure definition includes everything from the CREATE PROCEDURE statement to the batch terminator
- Lessons Learnt: Do NOT multi-task!
Until we meet next time,
SQLTwins by Nakul Vachhrajani 