Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 Naming stored procedures
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

hardeepvirdee
Starting Member

United Kingdom
2 Posts

Posted - 11/07/2001 :  04:13:17  Show Profile  Reply with Quote
Sorry, just realised I posted this to the wrong board. Its meant to posted in the discussion about naming conventions - this is my first post. Hopefully one of the moderators will move it.

----------------------------------------

Just a word of caution regarding naming stored procedures, I'm sure many of you may already know this - though I've seen it done in msdn magazine a few times.

Using 'sp_' as a prefix is not a good idea, because SQL Server will ALWAYS check the master database first for the stored procedure, before checking the current database. This is why you can access all the system stored procedures from any database. Try it out - place one of your stored procedures in the master database and you will be able to call it from another, even without using 'exec master..sp_myStoredProc' syntax.

This can have a impact on performance, though how much of an impact I don't know.

When naming my stored procedures I use the following rules:

1. two letter prefix to give the natural grouping e.g. 'ac' for accounts
2. the verb describing the action i.e. Get, Check, Update, Delete and Insert
3. the name of the actioned entity - this should match the table name is some way
4. and if applicable what parameters are used proceded with 'by'

for example:

acGetPaymentsbyUserID
acGetPaymentsbyDateRange
acGetPaymentsbyDateRangeAndUserID
acUpdatePaymentByUserID
acInsertPayment
usInsertUser
usDeleteUserbyUserID

I find this leads to an easy way to know what your stored procedure does and allows natural grouping of them when in lists like the data view in visual interdev. Of course you have to be flexiable and I have used underscores and deviated from this when it helps.

Edited by - hardeepvirdee on 11/07/2001 04:28:52

izaltsman
A custom title

USA
1139 Posts

Posted - 11/07/2001 :  08:47:16  Show Profile  Send izaltsman an AOL message  Send izaltsman an ICQ Message  Reply with Quote
quote:

Using 'sp_' as a prefix is not a good idea, because SQL Server will ALWAYS check the master database first for the stored procedure, before checking the current database.




Actually it is not true. I know there are articles out there that say it is, but in fact, SQL Server always tries to use the proc in the current database first, and only if the procedure by that name does not exist, it starts looking in master. Here is a simple test to prove it:



use master
go
create procedure dbo.sp_test_name_resolution
as
print 'Executing stored proc located in Master'
go

use pubs
go
create procedure dbo.sp_test_name_resolution
as
print 'Executing stored proc located in user db'
go

--While you are still in pubs, run this
exec sp_test_name_resolution

go


I tried running that script on both SQL7 and SQL2k, and the procedure in user db is always the one that runs. You may wish to avoid using "sp_" prefix in your stored procedure names to eliminate confusion, but as far as performance is concerned, you do not incur any performance penalty if you use it...


Edited by - izaltsman on 11/07/2001 08:54:33
Go to Top of Page

hardeepvirdee
Starting Member

United Kingdom
2 Posts

Posted - 11/07/2001 :  10:24:52  Show Profile  Reply with Quote
Thanks for the correction... obviously I shouldn't take what I read at face value and check things out for myself - even if it's in microsoft's own documentation!



Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 11/07/2001 :  15:50:15  Show Profile  Visit nr's Homepage  Reply with Quote
Still not quite true (think it might depend on version).
For user SPs it will use the one in the database in preference.
For system SPs it will use the one in master.
This can cause a few problems if you accidentally pick a name of a system SP.


==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000