Using SP_

Question

Using SP_

Viewing 15 posts - 31 through 45 (of 52 total)

You must be logged in to reply to this topic. Login to reply

Revenant SSC-Forever Points: 42467 More actions · Answer 1

bitbucket-25253 (12/14/2011)
Jeff Moden (12/14/2011)
BWAA-HAAA!!! Judging by the fact that more people got the wrong answer than the correct one, I'd say you just taught a whole bunch of people a lesson, Ron. Well done! 🙂
Coming from some one like yourself and your anti-RBAR, quirky update and so many other articles which teach so many so much (and I am one of those you have taught) I thank you for the compliment

I wish I said that.

Jeff Moden SSC Guru Points: 1003867 More actions · Answer 2

Revenant (12/14/2011)
bitbucket-25253 (12/14/2011)
Jeff Moden (12/14/2011)
BWAA-HAAA!!! Judging by the fact that more people got the wrong answer than the correct one, I'd say you just taught a whole bunch of people a lesson, Ron. Well done! 🙂
Coming from some one like yourself and your anti-RBAR, quirky update and so many other articles which teach so many so much (and I am one of those you have taught) I thank you for the compliment
I wish I said that.

Thank you both for the wonderful feedback but this one is all Ron and a very well chosen/written question. When I first looked at the question, I thought 98% would get it right. Amazing the way it turned out.

Ron, there's a whole lot of information and misinformation out there about sp_* especially since code no longer really lives in master but in the mostly-hidden "resources" DB. This would make a wonderful article even if it were only a short "Spackle" article.

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

Thanks Ron. This is good to know.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

john.arnott SSChampion Points: 11882 More actions · Answer 4

Hugo Kornelis (12/14/2011)
This actually makes the behaviour even more weird, since (according to the documentation for SQL Server 2005 - I see the relevant comments have been removed from the SQL 2008 docs, which is even more disturbing) stored procedures with a name starting with sp_ are searched in master first, user DB second.

Yes, weird as heck. But now I'm really confused about the precedence of similarly-named procedures. Running this on SQL Express 2005, the procedure in user db "Experiment" is executed while connected to that db, while the master db version is run while in master or any other db (like model).

use master

go

If object_ID('sp_myproc') is not null

drop procedure sp_myproc

go

Create procedure sp_myproc --create in master

as Select 'sp in master db.' as ProcOutput

go

Exec sp_myproc

go

use Experiment

go

If object_ID('sp_myproc') is not null

drop procedure sp_myproc

go

Create procedure sp_myproc --create in experiment

as Select 'sp in Experiment db.' as ProcOutput

go

Exec sp_myproc

go

use master

go

Exec sp_myproc

go

use model

go

Exec sp_myproc

go

edit to correct mistaken comments in the sql code.

john.arnott SSChampion Points: 11882 More actions · Answer 5

After finding the BOL doc that warns against naming a procedure with an "sp_" prefix, I wonder if the precedence rules are controlled by another factor besides the prefix and database. The doc does say that a procedure whose name matches a system procedure would be overridden, but perhaps we're assuming too much that if we create our own "sp_xxxxxx" in the master db, that it would have the same preeminent status.

If your application uses nonschema qualified name references and your own procedure name conflicts with a system procedure name, your application will break because the name binds to the system procedure, not your own.

Is there another factor besides the sp_ prefix and being in the master db that controls this? Perhaps the key to this is in that "resources" db that Jeff mentions.

TomThomson SSC Guru Points: 104773 More actions · Answer 6

john.arnott (12/15/2011)
Is there another factor besides the sp_ prefix and being in the master db that controls this? Perhaps the key to this is in that "resources" db that Jeff mentions.

Yes, there is some other factor, but I haven't a clue what it is. In fact many stored procs whose name begins sp_ and which are supplied by MS as part of SQLServer, referred to in BoL as system procs, and listed by SMSS object explorer as system stored procs turn out not to be system stored procs at all as was pointed out to me recently by one of the SQLServerCenter gurus (can't remember who).

Tom

Paul White SSC Guru Points: 150467 More actions · Answer 7

Reposting these links from earlier, for those looking for the details:

http://blogs.msdn.com/b/sqlprogrammability/archive/2006/04/03/567643.aspx

http://blogs.msdn.com/b/sqlprogrammability/archive/2006/04/03/567648.aspx

http://blogs.msdn.com/b/sqlprogrammability/archive/2006/04/03/567660.aspx

http://blogs.msdn.com/b/sqlprogrammability/archive/2006/04/03/567663.aspx

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Chris Umbaugh Ten Centuries Points: 1284 More actions · Answer 8

Did not know that. Thanks!

Chris Umbaugh
Data Warehouse / Business Intelligence Consultant
twitter @ToledoSQL

Britt Cluff SSCertifiable Points: 5083 More actions · Answer 9

Good question, thanks for submitting. Not everyone is aware of this functionality and I've ran into it on several occasions.

http://brittcluff.blogspot.com/

john.moreno Default port Points: 1485 More actions · Answer 10

Toreador (12/14/2011)
Nice question, but not sure about the explanation. Surely the crucial point is that object_id('sp_mytable') is null, even though a select against that table recognises it (and a drop would drop it)?

Well, I'd say there were two crucial points, (1) that you can refer to a table in master without a fully qualified name while in another database, and (2) that object_id requires the fully qualifed name if the object is not in the current database.

I think the answer would be improved by pointing out the inconsistent behavior, as it is, without an explanation, the 2nd drop serves as a guard to ensure the correct response, but there's no explanation as to why it's necessary (or the consequences of using it if your QOD table happens to already contain a sp_mytable).

Rafael Krisller Right there with Babe Points: 737 More actions · Answer 11

Rafael Krisller

Right there with Babe

Points: 737

December 22, 2011 at 6:18 am

#1424757

Nice!

I forgot that.

Bru Medishetty SSC-Insane Points: 23606 More actions · Answer 12

Good Question...

Bru Medishetty

Blog -- LearnSQLWithBru

Join on Facebook Page Facebook.comLearnSQLWithBru

Twitter -- BruMedishetty

nikhildas Newbie Points: 1 More actions · Answer 13

It's a tricky question...!!

Nice...

Thanks & Regards,

Nikhildas

http://www.mssqlhelp.blogspot.com

Steve Vassallo SSCommitted Points: 1635 More actions · Answer 14

Great question and great information. Always good to learn something new or re-learn the forgotten!

Sathishk Right there with Babe Points: 738 More actions · Answer 15

Great question... Is that mean when any object with SP_ prefix used in combination with if (as in this case) would be searched in the current DB first and then will go for master DB? Even if so, won't the if statement given in the question do a second search in the master DB automatically? I am bit confused... :rolleyes: