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


Stored Procedure Execution


Stored Procedure Execution

Author
Message
Thierry Steenberghs
Thierry Steenberghs
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1099 Visits: 55
this does not work as explained with SQL 2008 R2.
sp or not, the local version of the stored procedure is executed first.
OCTom
OCTom
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4053 Visits: 4152
Executing the code as written... won't it return 5? "Use Adventureworks" points the "EXECUTE sp_ReturnSomething 5" to Adventureworks and runs that proc. What am I missing this early in the A.M.?
sjimmo
sjimmo
SSCarpal Tunnel
SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)

Group: General Forum Members
Points: 4704 Visits: 2907
Avoiding uage of prefix sp_ improves the performance


When a stored procedure is executed using "sp_", SQL Server checks in the master first, as "sp_" is assumed to be reserved for a system stored procedure. Thus, the performance improvemnet would be caused by not having to go to the system to look first for the procedure.

In this case, executing the procedure as written causes the process to be executed from the master (system) where the code is
SELECT @Input + 2 AS Result


Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan
Cliff Jones
Cliff Jones
SSCertifiable
SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)

Group: General Forum Members
Points: 5127 Visits: 3648
prashant.bhatt (6/9/2010)
Very Nice question

I am not sure but the query is returning me 5....

I dont have the AdventureWorks db on the server...

Tried using the test database with dbo schema.... may be this is why the results are different for me


Yes, if DBO is your default schema and you create the stored procedure in the DBO schema that returns 5, then that is the version of the stored procedure that will execute.
Daniel Bowlin
Daniel Bowlin
SSCertifiable
SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)

Group: General Forum Members
Points: 7582 Visits: 2629
Great question, My first thought was the correct answer, but then I noticed that the procedure in the Master db was not prefixed by a schema so I had to do some reading to see how that would be handled.

I think the lesson here is never use sp in your stored procedure names and always qualify your calls.
Oleg Netchaev
Oleg Netchaev
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2159 Visits: 1816
Very good question, thank you Jason.

Creating a proc with name beginning with sp_ is very evil indeed. While there is a way to invoke the AdventureWorks' version of the proc by schema qualifying it, i.e.

exec Person.sp_ReturnSomething 5;


returns desired

Result
-----------
5



still beginning proc name with sp_ is ill advised for a very good reason.

Oleg
SanjayAttray
SanjayAttray
SSCertifiable
SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)

Group: General Forum Members
Points: 5585 Visits: 1619
Instead of Person.sp_ReturnSomething, create proc with dbo prefix and it returns 5.

I got it wrong as I don't have Adventureworks DB. But, with testing it and getting right would be cheating myself.

Good question. Everyday is a learning day.

SQL DBA.
SQLRNNR
SQLRNNR
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: General Forum Members
Points: 63531 Visits: 18570
Thanks - it is a good question, good reminder, and good discussion.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

Nadrek
Nadrek
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4276 Visits: 2741
Reasons I recommend:
A) Never have the first three letters of any stored user procedure be "sp_"

B) Always use three part naming, so you either execute:
Adventureworks.Person.(otherprefix)ReturnSomething
or
Master.dbo.(otherprefix)ReturnSomething

both of which make it explicitly obvious what you're calling.

Ambiguity is a playground of subtle bugs.
sjimmo
sjimmo
SSCarpal Tunnel
SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)

Group: General Forum Members
Points: 4704 Visits: 2907
Ambiguity is a playground of subtle bugs


I like it.

Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan
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