August 11, 2015 at 11:33 am
In our of our recent install scripts was the following statement. Note that there is no GO statement after the END statement when creating the procedure. We found that on SOME servers this caused the object to be created, you can see it in the explorer and can sp_helptext it. However, when you access it from the application the DB returns object doesn't exist or you do not have permissions. Also odd is that if you backup and restore the DB to another server, in some servers the object exists.
If we put the GO statement at the end of creating the procedure all is well.
Any thoughts on why there's different behavior on different servers?
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[procGetR0021]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[procGetR0021]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[procGetR0021]
(@stage INT)
AS BEGIN
-- Do stuff here
-- End of doing stuff
END
-- Note this next statement creates custom extended properties so we can query for them
-- in production encrypted objects
BEGIN
DECLARE @propThisObj nvarchar(132) = N'procGetR0021'
DECLARE @propVersion nvarchar(80) = N'Release Number'
DECLARE @propNote nvarchar(132) = N'Custom note'
EXECUTE Set_Extended_Property_Proc @propThisObj, @propVersion, @propNote;
END
GO
August 11, 2015 at 12:50 pm
The lack of GO at the appropriate location means that the procedure would then include the statements up through the GO at the end of your code block. So the procedure will exist, but it may not pass validation when you attempt to execute it, due to what is, effectively, invalid content, and thus the error that comes back. That muich makes sense. Not seeing the object at all would suggest differing circumstances at creation time, or possibly different SQL Server versions, or maybe the content following where the GO would normally be ends up causing the procedure to NOT get created. If that "additional content" is different for different servers or procedures, you might easily have differing results on different servers. Try to validate that you did indeed have the EXACT same script running on ALL the affected machines, and that all SQL Server versions and Service Packs are identical.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 11, 2015 at 1:49 pm
As you might have noticed, BEGIN and END won't define the begin and end of a stored procedure.
A stored procedure must be the only statement in the batch, so anything after CREATE PROCEDURE will be part of the procedure (anything before, except for comments, will generate an error). To define the end of a procedure, the only "coded" option is to use GO to separate the batch.
For the behavior differences, I'm not sure on what is happening.
August 11, 2015 at 3:58 pm
Luis Cazares (8/11/2015)
As you might have noticed, BEGIN and END won't define the begin and end of a stored procedure.A stored procedure must be the only statement in the batch, so anything after CREATE PROCEDURE will be part of the procedure (anything before, except for comments, will generate an error). To define the end of a procedure, the only "coded" option is to use GO to separate the batch.
For the behavior differences, I'm not sure on what is happening.
Just remember that the "GO" batch separator is an SSMS option. You can change the batch separator to anything that you want in the Options / Query Execution tab.
And, as Jeff has in his signature, just because you "can" do something doesn't mean that you "should" do it. In this case, please don't change it.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
August 12, 2015 at 5:19 am
Thanks for the replies. I always thought that the BEGIN and END statements defined the procedure. Thanks for the schooling. This was a case where a GO was missed (we always use them) and no error was generated.
Thanks again
ST
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply