SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


The Funny Stored Procedure Name


The Funny Stored Procedure Name

Author
Message
Sean Lange
Sean Lange
SSC Guru
SSC Guru (269K reputation)SSC Guru (269K reputation)SSC Guru (269K reputation)SSC Guru (269K reputation)SSC Guru (269K reputation)SSC Guru (269K reputation)SSC Guru (269K reputation)SSC Guru (269K reputation)

Group: General Forum Members
Points: 269800 Visits: 19765
paul s-306273 - Friday, February 8, 2019 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.


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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
George Vobr
George Vobr
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8304 Visits: 4067
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 Smile
Shayn Thomas
Shayn Thomas
SSCarpal Tunnel
SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)

Group: General Forum Members
Points: 4857 Visits: 887
how random
Never going to use it but fun nonetheless

---------------------------------------------------------------------------------------
The more you know, the more you know that you dont know
Luis Cazares
Luis Cazares
SSC Guru
SSC Guru (172K reputation)SSC Guru (172K reputation)SSC Guru (172K reputation)SSC Guru (172K reputation)SSC Guru (172K reputation)SSC Guru (172K reputation)SSC Guru (172K reputation)SSC Guru (172K reputation)

Group: General Forum Members
Points: 172730 Visits: 23044
Toreador - Friday, February 8, 2019 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...

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.



Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?


How to post data/code on a forum to get the best help: Option 1 / Option 2
Eirikur Eiriksson
Eirikur Eiriksson
SSC Guru
SSC Guru (172K reputation)SSC Guru (172K reputation)SSC Guru (172K reputation)SSC Guru (172K reputation)SSC Guru (172K reputation)SSC Guru (172K reputation)SSC Guru (172K reputation)SSC Guru (172K reputation)

Group: General Forum Members
Points: 172588 Visits: 24489
Steve Jones - SSC Editor - Friday, February 8, 2019 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.

I stand corrected
Cool

Fred_unique
Fred_unique
SSC Rookie
SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)

Group: General Forum Members
Points: 26 Visits: 35
Toreador - Friday, February 8, 2019 1:52 AM
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...

I agree with Toreador on this except that you also said it's a security hole. Explaining how it's insecure might be useful.

Steve Jones
Steve Jones
SSC Guru
SSC Guru (651K reputation)SSC Guru (651K reputation)SSC Guru (651K reputation)SSC Guru (651K reputation)SSC Guru (651K reputation)SSC Guru (651K reputation)SSC Guru (651K reputation)SSC Guru (651K reputation)

Group: Administrators
Points: 651197 Visits: 21472
The reason I added this, and a few other questions, is that you may run into this and should understand how it works. Knowledge might be helpful and the discussion may give you knowledge or reasons to argue against continuing this practice.

Always remember your frame of reference may change, so having more information, to me, is valuable.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (936K reputation)SSC Guru (936K reputation)SSC Guru (936K reputation)SSC Guru (936K reputation)SSC Guru (936K reputation)SSC Guru (936K reputation)SSC Guru (936K reputation)SSC Guru (936K reputation)

Group: General Forum Members
Points: 936049 Visits: 49079
I don't know why people call ancient feature a "security hole". I And the only time it's actually a risk is if you go to drop the proc. You can't define which part to drop. It's all the procs in the numbered "group".

We didn't use it for versioning back in the day. We used it to number steps in an execution order. I say "we" but it wasn't me. It was someone else's idea at a company I worked at a very long time ago. It did help de-declutter things a bit but I still wouldn't use it even if it weren't deprecated because you can't drop a single numbered part. You can, however, alter a numbered part.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

When you put the right degree of spin on it, the number 318 is also a glyph that describes the nature of a DBAs job. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (936K reputation)SSC Guru (936K reputation)SSC Guru (936K reputation)SSC Guru (936K reputation)SSC Guru (936K reputation)SSC Guru (936K reputation)SSC Guru (936K reputation)SSC Guru (936K reputation)

Group: General Forum Members
Points: 936049 Visits: 49079
Steve Jones - SSC Editor - Friday, February 8, 2019 8:29 AM
The reason I added this, and a few other questions, is that you may run into this and should understand how it works. Knowledge might be helpful and the discussion may give you knowledge or reasons to argue against continuing this practice.

Always remember your frame of reference may change, so having more information, to me, is valuable.

The question about a replacement for it recently came up on these very forums.


--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

When you put the right degree of spin on it, the number 318 is also a glyph that describes the nature of a DBAs job. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Solomon Rutzky
Solomon Rutzky
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14348 Visits: 3511
Toreador - Friday, February 8, 2019 1:52 AM
...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...

I agree with Steve on this one (he gave his reason in another reply). To state it a little differently: the fact that the feature can still be used means that someone might suggest that you use it. Knowing what it does and why you shouldn't use it is certainly beneficial, as it will help prevent the situation where you unwittingly use it. Deprecated doesn't mean that it will ever be fully removed. So it never really hurts to know as much as possible about the environment in which we are working. People often use inferior / problematic features because they haven't been removed and some people don't know any better, such as not using TEXT / NTEXT / IMAGE datatypes, and a lot of other things. Remember, "knowing is half the battle!" Wink

Take care, Solomon..


SQL# - https://SQLsharp.com/ ( SQLCLR library of over 340 Functions and Procedures)
Sql Quantum Lift - https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap - https://SqlQuantumLeap.com/ ( blog )
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum









































































































































































SQLServerCentral


Search