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 Tuesday, June 8, 2010 9:59 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 7:20 AM
Points: 1,534, Visits: 2,261
Comments posted to this topic are about the item Stored Procedure Execution

The Redneck DBA
Post #934369
Posted Tuesday, June 8, 2010 10:00 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, September 5, 2014 2:00 PM
Points: 2,160, Visits: 2,191
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... ;)
Post #934370
Posted Tuesday, June 8, 2010 10:50 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, November 2, 2014 11:03 PM
Points: 2,103, Visits: 371
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 ?
Post #934384
Posted Tuesday, June 8, 2010 11:08 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, September 14, 2010 8:24 AM
Points: 69, Visits: 33
Nailed it, because I assumed that "person" was not me. If I were "person" the answer would have been 5.
Post #934388
Posted Tuesday, June 8, 2010 11:13 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, September 14, 2010 8:24 AM
Points: 69, Visits: 33
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.
Post #934390
Posted Wednesday, June 9, 2010 12:50 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 11:09 PM
Points: 1,940, Visits: 1,173
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)
Post #934420
Posted Wednesday, June 9, 2010 1:03 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, November 20, 2014 7:01 AM
Points: 3,329, Visits: 1,323
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.
Post #934424
Posted Wednesday, June 9, 2010 2:10 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, July 5, 2010 10:56 PM
Points: 230, Visits: 17
Avoiding uage of prefix sp_ improves the performance.
Post #934440
Posted Wednesday, June 9, 2010 2:27 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 7:16 AM
Points: 118, Visits: 174
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
Post #934448
Posted Wednesday, June 9, 2010 4:45 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:20 AM
Points: 5,367, Visits: 8,986
Good question Jason!

Wayne
Microsoft Certified Master: SQL Server 2008
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, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #934518
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse