November 24, 2015 at 3:40 am
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
November 24, 2015 at 5:50 am
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.
November 24, 2015 at 7:54 am
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.
December 1, 2015 at 10:33 am
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.
December 1, 2015 at 10:42 am
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.
December 1, 2015 at 10:43 am
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.
December 1, 2015 at 5:37 pm
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
Change is inevitable... Change for the better is not.
December 3, 2015 at 2:13 am
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
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply