Results 1 to 11 of 11

Thread: Difference between system stored proc and user defined stored proc?

  1. Dec 21st, 2005, 09:37 AM #1
    crptcblade
    crptcblade is offline

    Thread Starter
    The Devil crptcblade's Avatar
    Join Date
    Aug 2000
    Location
    Quetzalshacatenango
    Posts
    9,091

    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
    Laugh, and the world laughs with you. Cry, and you just water down your vodka.


    Take credit, not responsibility
    Reply With Quote Reply With Quote
  2. Dec 22nd, 2005, 01:40 AM #2
    kishore.kr
    kishore.kr is offline
    Addicted Member
    Join Date
    Jul 2004
    Location
    Mumbai
    Posts
    236

    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,
    --Kishore...
    Reply With Quote Reply With Quote
  3. Dec 22nd, 2005, 08:25 AM #3
    crptcblade
    crptcblade is offline

    Thread Starter
    The Devil crptcblade's Avatar
    Join Date
    Aug 2000
    Location
    Quetzalshacatenango
    Posts
    9,091

    Re: Difference between system stored proc and user defined stored proc?

    Excellent, thanks
    Laugh, and the world laughs with you. Cry, and you just water down your vodka.


    Take credit, not responsibility
    Reply With Quote Reply With Quote
  4. Dec 22nd, 2005, 08:35 AM #4
    mendhak
    mendhak is offline
    I'm about to be a PowerPoster! mendhak's Avatar
    Join Date
    Feb 2002
    Location
    Ulaan Baator GooGoo: Frog
    Posts
    38,170

    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.
    Reply With Quote Reply With Quote
  6. Dec 22nd, 2005, 09:55 AM #5
    abhijit
    abhijit is offline
    PowerPoster abhijit's Avatar
    Join Date
    Jun 1999
    Location
    Chit Chat Forum.
    Posts
    3,228

    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.
    Everything that has a computer in will fail. Everything in your life, from a watch to a car to, you know, a radio, to an iPhone, it will fail if it has a computer in it. They should kill the people who made those things.- 'Woz'
    save a blobFileStreamDataTable To Text Filemy blog
    Reply With Quote Reply With Quote
  7. Dec 22nd, 2005, 10:49 AM #6
    kishore.kr
    kishore.kr is offline
    Addicted Member
    Join Date
    Jul 2004
    Location
    Mumbai
    Posts
    236

    Re: Difference between system stored proc and user defined stored proc?

    OK. Thanks for pointing it out.
    Cheers,
    --Kishore...
    Reply With Quote Reply With Quote
  8. Dec 22nd, 2005, 03:01 PM #7
    mendhak
    mendhak is offline
    I'm about to be a PowerPoster! mendhak's Avatar
    Join Date
    Feb 2002
    Location
    Ulaan Baator GooGoo: Frog
    Posts
    38,170

    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?
    Reply With Quote Reply With Quote
  9. Dec 22nd, 2005, 03:58 PM #8
    abhijit
    abhijit is offline
    PowerPoster abhijit's Avatar
    Join Date
    Jun 1999
    Location
    Chit Chat Forum.
    Posts
    3,228

    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.
    Everything that has a computer in will fail. Everything in your life, from a watch to a car to, you know, a radio, to an iPhone, it will fail if it has a computer in it. They should kill the people who made those things.- 'Woz'
    save a blobFileStreamDataTable To Text Filemy blog
    Reply With Quote Reply With Quote
  11. Dec 22nd, 2005, 06:23 PM #9
    kaffenils
    kaffenils is offline
    Fanatic Member kaffenils's Avatar
    Join Date
    Apr 2004
    Location
    Norway
    Posts
    946

    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.


    Tutorial: SQL Server recursion
    Small ADO demo
    Reply With Quote Reply With Quote
  12. Dec 22nd, 2005, 06:57 PM #10
    szlamany
    szlamany is offline
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    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!

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:     [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008
    Reply With Quote Reply With Quote
  13. Dec 23rd, 2005, 09:18 AM #11
    kishore.kr
    kishore.kr is offline
    Addicted Member
    Join Date
    Jul 2004
    Location
    Mumbai
    Posts
    236

    Re: Difference between system stored proc and user defined stored proc?

    That is really a useful tip.
    --Kishore...
    Reply With Quote Reply With Quote
« Previous Thread | Next Thread »

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

Forum Rules


Click Here to Expand Forum to Full Width