Difference between system stored proc and user defined stored proc?
Is there a way, from the sysobjects table or whatever, to tell the difference between a system stored proc and a user defined one?
Thanks
Re: Difference between system stored proc and user defined stored proc?
hi
select * from sysobjects where xtype='P' and category=0
will retrieve all the user stored procedures
and the rest are system stored procedures. you can obviously verify system stored procedures with prefix "sp_" .
Cheers,
Re: Difference between system stored proc and user defined stored proc?
Re: Difference between system stored proc and user defined stored proc?
Don't rely on the sp_ prefix though, some coders actually add that to their SPs without understanding its significance.
Re: Difference between system stored proc and user defined stored proc?
Yup, Mendhaks right. I am one of those coders. I add sp_ to everything. Including oracle stored procedures.
Re: Difference between system stored proc and user defined stored proc?
OK. Thanks for pointing it out.
Cheers,
Re: Difference between system stored proc and user defined stored proc?
Quote:
Originally Posted by abhijit
Yup, Mendhaks right. I am one of those coders. I add sp_ to everything. Including oracle stored procedures.
If you know its significance, why do you do it?
Re: Difference between system stored proc and user defined stored proc?
Thats the stupid standard they have in the shop I work. So I have to follow the standards.
Re: Difference between system stored proc and user defined stored proc?
Quote:
Originally Posted by abhijit
Thats the stupid standard they have in the shop I work. So I have to follow the standards.
Tell them to read Micosofts recommendation on naming stored procedures, and if they still say that you should put sp in the beginning of a sproc on SQL Server you should tell them to put their standard a place where the sun never shines. Well, maybe not that drastic, but you should really tell them the danger of beginning the name of a sproc with sp.
Reason: If you tell SQL Server to execute a procedure that begin with sp, it will first look for it in the master database and then in the current db. If there is a sproc by that name in master it will execute this and not the one in your database.
Re: Difference between system stored proc and user defined stored proc?
Avoiding sp_ and xp_ is a good practice - xp_ indicates extended-stored procedures - take a look at the MASTER database in EM or QA and open up the STORED PROCEDURE and EXTENDED STORED PROCEDURE branches - there are a lot of procedures that are "system sprocs"...
Some of these have pretty short and ambiguous names.
Plus having your own standards make more sense - for example!
frmMaster_View - that's the SPROC behind the VIEW button on the MASTER form for us
frmMaster_Inquire - that SPROC returns the textbox and flexgrid data for the MASTER form when INQUIRE is clicked.
frmMaster_Update - obvious...
frmMaster_View_Affil - that SPROC validates the AFFIL textbox on the MASTER form.
rptMemberList - report SPROC for a MEMBER LIST
infMemberCounts - info display SPROC for MEMBERSHIP COUNTS
getName_F - function (UDF) to fixup a name
getEligibility_P - procedure to get the eligibility of a member
ScrubTax_P - scrub the imported tax text file...
Silly stuff like this makes life much easier when an app has 1000 or so SPROCS!
Re: Difference between system stored proc and user defined stored proc?
That is really a useful tip.
