As most will know sp_ does not stand for stored procedure, it stands for system stored procedure. But calling your procedure sp_something doesn't make it a system procedure automatically, it just hints the server how to resolve the procedure.
When a procedure that starts with sp_ is called, first the master database is checked if it is a real system stored procedure. The books online shows this behavior by creating a procedure in AdventureWorks called dbo.sp_who. However, since sp_who is a real system stored procedure, the existence of AdventureWorks.dbo.sp_who is always ignored. If the procedure is not a real system stored procedure, the connected database is checked for the existence of the stored procedure. If it is in the database you're currently connected to, it gets executed. If it isn't in the database you're currently connected to, it is retrieved from master (or you receive an error if it isn't there either). You can verify this behavior based on the following code.
USE AdventureWorksGOCREATE PROCEDURE sp_sayhelloASSELECT 'Hello from AdventureWorks, you are connected to ' + DB_NAME() + '.'GO USE masterGOCREATE PROCEDURE sp_sayhelloASSELECT 'Hello from master, you are connected to ' + DB_NAME() + '.'GO
Now, when executing sp_sayhello while connected to AdventureWorks, it will return;
Hello from AdventureWorks, you are connected to AdventureWorks
With any other database, say msdb, you get the following result.
Hello from master, you are connected to msdb
So there are two reasons why starting your stored procedure name with sp_ isn't smart;
There is however one scenario where creating stored procedures with sp_ is smart: When you create it in master as part of your own standardized way of working. Creating your own toolbox so to say. With SQL Server 2005 and 2008 there is an automatic separation, your sp_ procedures are created in the dbo schema by default and the real system stored procedures reside in the sys schema (the actual system stored procedures are in the mssqlsystemresource database).
Your own sp_ procedures and schemas: DON'T!!! It does not work if the schema in master isn't dbo.
USE masterGOCREATE SCHEMA toolboxGOCREATE PROCEDURE toolbox.sp_sayhelloASSELECT 'Hello from master. You are connected to ' + DB_NAME() + '.'GOUSE AdventureWorksGOEXEC sp_sayhello -- Doesn't workEXEC toolbox.sp_sayhello -- Doesn't workEXEC master.toolbox.sp_sayhello -- Executes against master, not AdventureWorks.GOUSE masterGODROP PROCEDURE toolbox.sp_sayhelloGODROP SCHEMA toolboxGO
Your own sp_ procedures and non-privileged users: Make sure the login has permissions to execute the procedure from master and that any needed permissions are held in the target database. To illustrate, a login, mapped to a user in AdventureWorks will execute a stored procedure named sp_maketable. To make this work, public (therefor any login through guest, which is appropriate for master) will receive execute permissions on the procedure and create table and alter schema permissions are granted to the user in AdventureWorks. The table is created in the default schema of the user.
USE masterGOCREATE PROCEDURE sp_maketableASCREATE TABLE tblTest (col1 int)GOGRANT EXECUTE ON dbo.sp_maketable TO public -- Make sure permissions allow the user to execute.GOCREATE LOGIN np_user WITH PASSWORD = 'secret', DEFAULT_DATABASE = AdventureWorksGOUSE AdventureWorksGOCREATE USER np_user FOR LOGIN np_user WITH DEFAULT_SCHEMA = SalesGOGRANT CREATE TABLE TO np_user -- Make sure the user has proper permissions in the database.GOGRANT ALTER ON SCHEMA::Sales TO np_user -- Make sure the user has proper permissions in the schema.GOEXECUTE AS LOGIN = 'np_user'GOSELECT SUSER_SNAME(), USER_NAME() -- Verify it is executing as the user.GOEXEC sp_maketableGOREVERT
Important stuff when writing your own sp_ 's: