February 7, 2019 at 8:54 pm
Comments posted to this topic are about the item The Funny Stored Procedure Name
February 7, 2019 at 10:00 pm
Grief, haven't seen or used this since SQL2000
Nice bit of trivia, thanks, Steve
____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
February 8, 2019 at 12:52 am
Good question, thanks Steve.
...
February 8, 2019 at 1:52 am
Guessed right but for the wrong reason.
Must admit, I don't quite see the point in teaching us about a feature that most of us have probably never heard of if that feature is deprecated so we won't ever use it...
February 8, 2019 at 2:04 am
Knew it would be 1 or 2 and went for 2 as the procedure is dropped when the connection is closed.
Aagh.
February 8, 2019 at 2:24 am
Normally I don't post clues to the questions answers but this time the answer is not correct.
There is an older feature of CREATE PROCEDURE that allows you to create versions of stored procedures. You do this with a semicolon and a number after the name.
This only creates one object in sys.objects.
This is not accurate as multiple procedures are created and can be called separately. Further, the default N for procedure_name;N is 1, hence all procedures can be called with the suffix ";1".
😎
Example code
USE TEEST;
GO
SET NOCOUNT ON;
GO
CREATE PROCEDURE GetOne
AS
SELECT 1
GO
CREATE PROCEDURE GetOne;2
AS
SELECT 2
GO
CREATE PROCEDURE GetOne;3
AS
SELECT 3
GO
SELECT *
FROM sys.objects AS o
WHERE o.type = 'P';
EXEC GetOne;
EXEC GetOne;1
EXEC GetOne;2
EXEC GetOne;3
The four executions will produce 1,1,2,3 respectfully.
Then scripting the procedure as create produces this code
USE [TEEST]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[GetOne]
AS
SELECT 1
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[GetOne];2
AS
SELECT 2
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[GetOne];3
AS
SELECT 3
GO
February 8, 2019 at 3:08 am
Eirikur Eiriksson - Friday, February 8, 2019 2:23 AMNormally I don't post clues to the questions answers but this time the answer is not correct.There is an older feature of CREATE PROCEDURE that allows you to create versions of stored procedures. You do this with a semicolon and a number after the name.
This only creates one object in sys.objects.
This is not accurate as multiple procedures are created and can be called separately. Further, the default N for procedure_name;N is 1, hence all procedures can be called with the suffix ";1".
😎
I think Steve is specifically talking about how many objects appear in sys.objects, not how many objects are actually created. Yes, they were all created, and yes, you can execute them all, however, only 1 object (with the name GetOne) appears in sys.objects (and sys.procedures). If, however, you review sys.numbered_procedures you'll see the objects listed (except for revision 1).
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
February 8, 2019 at 6:06 am
The description "version number" vs "an optional integer that is used to group procedures of the same name" has a bit of a semantic disconnect that took me several minutes to reconcile after I read the answer and compared it to the link.
personally, I am glad its in maintenance mode and not standard practice, seems like a great way to really shoot yourself in the foot.
February 8, 2019 at 6:33 am
latkinson - Friday, February 8, 2019 6:06 AMThe description "version number" vs "an optional integer that is used to group procedures of the same name" has a bit of a semantic disconnect that took me several minutes to reconcile after I read the answer and compared it to the link.
personally, I am glad its in maintenance mode and not standard practice, seems like a great way to really shoot yourself in the foot.
I agree, I can't think of a case where it wouldn't be better to add "_2" to the procedure name instead.
February 8, 2019 at 7:05 am
The question asks how many rows are returned from sys.objects. There is only 1. This is accurate and correct.
As to why or where you would use this, I'm not sure. It's a security hole and a potential source of problems with no real benefit. This question was created as I was unaware of this until recently, but others have used it. If you came upon this, my advice would be to remove this from your system and force people to update code for things like GetOne2, GetOne3, etc.
February 8, 2019 at 7:15 am
paul s-306273 - Friday, February 8, 2019 2:04 AMKnew it would be 1 or 2 and went for 2 as the procedure is dropped when the connection is closed.
Aagh.
Why would the procedure be dropped when the connection is closed?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 8, 2019 at 7:26 am
Great question about a little known but not completely safe feature of SQL Server – numbered stored procedures.
Notice the use of semi-colon here. The semi-colon is not used as a statement terminator.
Thanks Steve, well done 🙂
February 8, 2019 at 7:33 am
how random
Never going to use it but fun nonetheless
---------------------------------------------------------------------------------------
The more you know, the more you know that you dont know
February 8, 2019 at 7:52 am
Toreador - Friday, February 8, 2019 1:52 AMGuessed right but for the wrong reason.
Must admit, I don't quite see the point in teaching us about a feature that most of us have probably never heard of if that feature is deprecated so we won't ever use it...
You could learn it in case someone else used it and you encounter it on a database that you just started to work on.
I learned about this when someone asked about it on the forums recently.
February 8, 2019 at 8:01 am
Steve Jones - SSC Editor - Friday, February 8, 2019 7:05 AMThe question asks how many rows are returned from sys.objects. There is only 1. This is accurate and correct.As to why or where you would use this, I'm not sure. It's a security hole and a potential source of problems with no real benefit. This question was created as I was unaware of this until recently, but others have used it. If you came upon this, my advice would be to remove this from your system and force people to update code for things like GetOne2, GetOne3, etc.
I stand corrected
😎
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply