Stored Procedure Execution

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Jason Shadonix » Stored Procedure Execution

32 posts, Page 2 of 4

««1 2 3 4 »»»

Stored Procedure Execution

Rate Topic

Display Mode

Topic Options

Author

Message

Thierry Steenberghs

Posted Wednesday, June 09, 2010 5:27 AM

Ten Centuries

Group: General Forum Members
Last Login: Monday, February 11, 2013 6:39 AM
Points: 1,081, Visits: 42

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

OCTom

Posted Wednesday, June 09, 2010 6:36 AM

SSCrazy

Group: General Forum Members
Last Login: Today @ 11:48 AM
Points: 2,015, Visits: 2,843

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

sjimmo

Posted Wednesday, June 09, 2010 7:08 AM

SSCrazy

Group: General Forum Members
Last Login: Today @ 7:23 AM
Points: 2,863, Visits: 2,466

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

Cliff Jones

Posted Wednesday, June 09, 2010 8:27 AM

Hall of Fame

Group: General Forum Members
Last Login: Today @ 12:39 PM
Points: 3,397, Visits: 3,405

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

Daniel Bowlin

Posted Wednesday, June 09, 2010 8:43 AM

SSCrazy

Group: General Forum Members
Last Login: Today @ 8:49 AM
Points: 2,672, Visits: 2,417

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

Oleg Netchaev

Posted Wednesday, June 09, 2010 8:51 AM

SSCommitted

Group: General Forum Members
Last Login: Today @ 10:53 AM
Points: 1,662, Visits: 1,709

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

SanjayAttray

Posted Wednesday, June 09, 2010 8:58 AM

Hall of Fame

Group: General Forum Members
Last Login: Friday, March 15, 2013 2:43 PM
Points: 3,924, Visits: 1,554

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

SQLRNNR

Posted Wednesday, June 09, 2010 9:53 AM

SSCoach

Group: General Forum Members
Last Login: Yesterday @ 1:07 PM
Points: 18,733, Visits: 12,332

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 2008

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

Nadrek

Posted Wednesday, June 09, 2010 10:45 AM

Say Hey Kid

Group: General Forum Members
Last Login: Yesterday @ 12:28 PM
Points: 675, Visits: 2,031

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

sjimmo

Posted Wednesday, June 09, 2010 10:51 AM

SSCrazy

Group: General Forum Members
Last Login: Today @ 7:23 AM
Points: 2,863, Visits: 2,466

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 »

32 posts, Page 2 of 4

««1 2 3 4 »»»

Permissions