Use of SP_

Question

Use of SP_

ben.brugman

SSChampion

Points: 13369
More actions
November 24, 2015 at 3:40 am

#322544

For an example see the code below.
So I create a stored procedure starting with a sp_ in the name.
In general the advise is not to do this.
Is there a better solution ?
Ben
USE master;
GO
CREATE PROCEDURE dbo.sp_do_something_usefull
AS
BEGIN
SELECT DB_NAME() CURRENT_db
select * from information_schema.tables
END
GO
EXEC sp_MS_marksystemobject N'sp_do_something_usefull'
use TestDB
exec sp_do_something_usefull
use master
drop procedure sp_do_something_usefull

Viewing 8 posts - 1 through 7 (of 7 total)

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

Ed Wagner SSC Guru Points: 287026 More actions · Answer 1

If you want to store the procedure in master and be able to execute it from other databases without having to specify the database, then you're doing it right. If you don't want this or the procedure is only applicable to the current database (and it isn't master) then you shouldn't use the "sp_" prefix for the name of the procedure.

The reason it isn't advisable is that any time you execute a procedure that starts with the "sp_" prefix, SQL Server checks the master databases for the existence of that procedure and uses it if it finds it. If not, it looks at the current database. This step is skipped if you simply use a different name.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Luis Cazares SSC Guru Points: 183706 More actions · Answer 2

ben.brugman (11/24/2015)
So I create a stored procedure starting with a sp_ in the name.
In general the advise is not to do this.

In general, the advise is to avoid naming stored procedures using the prefix sp_, but this is the exception of the rule and basically the reason of it.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

JustMarie SSCertifiable Points: 7771 More actions · Answer 3

Why not use _sp as a suffix? It still shows that the code is a stored procedure and lets you group things by name much more easily.

I've advocated that at most places I've worked.

_sp - stored procedure

_vw - view

_fn - function

etc...

For those who say that the SQL files don't need this they're right. But then I challenge them to look at a SQL file without opening it and tell me what it does. Makes the point nice and clear.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

JustMarie (12/1/2015)
Why not use _sp as a suffix? It still shows that the code is a stored procedure and lets you group things by name much more easily.
I've advocated that at most places I've worked.
_sp - stored procedure
_vw - view
_fn - function
etc...
For those who say that the SQL files don't need this they're right. But then I challenge them to look at a SQL file without opening it and tell me what it does. Makes the point nice and clear.

Naming a .sql file is not the same as naming a table, view, or procedure. When it comes to the script files that are used to create these, yes it does make sense to name them in a manner that tells you what the script will accomplish.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 5

JustMarie (12/1/2015)
Why not use _sp as a suffix? It still shows that the code is a stored procedure and lets you group things by name much more easily.
I've advocated that at most places I've worked.
_sp - stored procedure
_vw - view
_fn - function
etc...
For those who say that the SQL files don't need this they're right. But then I challenge them to look at a SQL file without opening it and tell me what it does. Makes the point nice and clear.

I would say that it's a waste of 3 characters.

I'm not sure what you're referring with sql files. Are you referring to script files? Those files might or might not have one or several object definitions. I don't check the objects from a sql file, I check them directly from the database.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 6

I avoid such "Hungarian Notation" in SQL Server as a general rule for the same reasons I avoided it back in my programming days. One example is that we have had to create views over existing tables to accomplish some given functionality (converting a table to a Partitioned View is one example). Since the Partitioned View needs to have the same name as what the table used to have (in our cases), it doesn't seem right to have a view with a "tbl_" prefix. The same goes when we decide to move tables to another database and have synonyms to point to the table. If we had to change code to look at objects with "sn_" prefixes instead of "tbl_" prefixes, we'd have to regression test huge amounts of code because it was changed (and yes, we're closely audited because we work with banks).

Another example is when you have a denormalized table that's causing issues and you need to split it into other tables and create a view of those tables using the original table name.

The eventuality of change just makes it not worthwhile to include the object type in the name for me.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ben.brugman SSChampion Points: 13369 More actions · Answer 7

JustMarie (12/1/2015)
Why not use _sp as a suffix? It still shows that the code is a stored procedure and lets you group things by name much more easily.
I've advocated that at most places I've worked.
_sp - stored procedure
_vw - view
_fn - function
etc...
For those who say that the SQL files don't need this they're right. But then I challenge them to look at a SQL file without opening it and tell me what it does. Makes the point nice and clear.

Maybe the following is not clear to everybody.

When the given example procedure is changed to a name with _sp, it still works but works different from exactly the same procedure with the sp_ as start of the name.

The showing of the tables was meant to make this clear.

Ben