Stored Procedure Execution

  • TheRedneckDBA

    SSChampion

    Points: 14001

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

    The Redneck DBA

  • UMG Developer

    SSChampion

    Points: 13482

    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

    SSCertifiable

    Points: 7161

    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

    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

    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

    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: 8770

    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

    Avoiding uage of prefix sp_ improves the performance.

  • prashant.bhatt

    SSC-Addicted

    Points: 476

    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: 95373

    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

    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: 11755

    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

    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: 10517

    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

    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.

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

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