Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««1234»»»

Stored Procedure Execution Expand / Collapse
Author
Message
Posted Wednesday, June 9, 2010 5:27 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, August 18, 2014 7:38 AM
Points: 1,081, Visits: 48
this does not work as explained with SQL 2008 R2.
sp or not, the local version of the stored procedure is executed first.

Post #934535
Posted Wednesday, June 9, 2010 6:36 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 10:50 AM
Points: 2,566, Visits: 3,854
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.?
Post #934557
Posted Wednesday, June 9, 2010 7:08 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 14, 2014 9:47 AM
Points: 2,914, Visits: 2,525
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
Post #934581
Posted Wednesday, June 9, 2010 8:27 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, September 2, 2014 8:36 AM
Points: 3,918, Visits: 3,638
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.
Post #934631
Posted Wednesday, June 9, 2010 8:43 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 7:35 AM
Points: 2,817, Visits: 2,563
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.
Post #934646
Posted Wednesday, June 9, 2010 8:51 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 9:21 AM
Points: 1,676, Visits: 1,757
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
Post #934655
Posted Wednesday, June 9, 2010 8:58 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, July 17, 2014 10:56 AM
Points: 3,924, Visits: 1,607
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.
Post #934664
Posted Wednesday, June 9, 2010 9:53 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 12:16 PM
Points: 17,646, Visits: 15,497
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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #934720
Posted Wednesday, June 9, 2010 10:45 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 12:31 PM
Points: 872, Visits: 2,408
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.
Post #934767
Posted Wednesday, June 9, 2010 10:51 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 14, 2014 9:47 AM
Points: 2,914, Visits: 2,525
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
Post #934773
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse