Stored Procedure Execution

Question

Post reply

Stored Procedure Execution

TheRedneckDBA

SSChampion

Points: 14001
More actions
June 8, 2010 at 9:59 pm

#236268

Comments posted to this topic are about the item Stored Procedure Execution
The Redneck DBA

Viewing 15 posts - 1 through 15 (of 31 total)

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

UMG Developer SSChampion Points: 13482 More actions · Answer 1

Great question, thanks Jason!

The old don't start your SP names with SP_

I really wish I knew all of this 13 years ago... 😉

ziangij SSCrazy Eights Points: 8066 More actions · Answer 2

thanks, great question... got it wrong though 🙁

A user-defined stored procedure that has the same name as a system stored procedure and is either nonqualified or is in the dbo schema will never be executed; the system stored procedure will always execute instead.

i have read the microsoft link; was wondering if the user-defined proc doesn't ever run then what is the purpose of creating that proc ? why can't an error be thrown instead ?

forjonathanwilson SSC Enthusiast Points: 139 More actions · Answer 3

Nailed it, because I assumed that "person" was not me. If I were "person" the answer would have been 5.

forjonathanwilson SSC Enthusiast Points: 139 More actions · Answer 4

ziangij (6/8/2010)
thanks, great question... got it wrong though 🙁
A user-defined stored procedure that has the same name as a system stored procedure and is either nonqualified or is in the dbo schema will never be executed; the system stored procedure will always execute instead.
i have read the microsoft link; was wondering if the user-defined proc doesn't ever run then what is the purpose of creating that proc ? why can't an error be thrown instead ?

1) the user defined proc can be run explicitly, perhaps in within an EXEC(string) used in a proc that built the command with the user name as argument.

2)Or if that particular user (joeFoo) runs sp_whatever, and his/her username is attached to another proc with the same name (joeFoo.sp_whatever), then THAT proc executes.

malleswarareddy_m SSCertifiable Points: 5847 More actions · Answer 5

I got wrong.Thought that it will give error the sp_ ...... procedure already exists if the procedure name alreay in master database.

Malleswarareddy
I.T.Analyst
MCITP(70-451)

Mighty SSCrazy Eights Points: 9063 More actions · Answer 6

forjonathanwilson (6/8/2010)
Nailed it, because I assumed that "person" was not me. If I were "person" the answer would have been 5.

Or if the default schema of the user executing it is person.

vignesh 38804 Old Hand Points: 338 More actions · Answer 7

Avoiding uage of prefix sp_ improves the performance.

prashant.bhatt SSC-Addicted Points: 476 More actions · Answer 8

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

Prashant Bhatt
Sr Engineer - Application Programming

WayneS SSC Guru Points: 95460 More actions · Answer 9

Good question Jason!

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Thierry Steenberghs Ten Centuries Points: 1217 More actions · Answer 10

this does not work as explained with SQL 2008 R2.

sp or not, the local version of the stored procedure is executed first.

OCTom SSChampion Points: 11785 More actions · Answer 11

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 SSChampion Points: 11139 More actions · Answer 12

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 SSChampion Points: 10547 More actions · Answer 13

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 SSC-Dedicated Points: 34566 More actions · Answer 14

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.