User defined stored procedures should not be prefixed with 'SP_'. IS IT SO?

Microsoft best practice says, User defined stored procedures should not be prefixed with 'SP_'. IS IT SO?

I have seen many blogs saying it is best practice not to use SP_ prefix to the user defined stored procedure. ???

The question of the hour is what it makes difference from system procedure if we create user defined stored procedure with SP_ prefix, and then we decide whether to stop writing stored procedure with SP_ prefix.

We will go into the depth to find out how it works when we declare stored procedure with SP_ prefix by taking enough of examples.

Example 1

I am creating a stored procedure in tempDatabase, then I am creating a user defined stored procedure with the name sp_tables.

create database tempDatabase

Command(s) completed successfully.

Database is ready.

use tempDatabase

go

CREATE PROCEDURE sp_tables

AS

BEGIN

      select * from EmployeeList

END

GO

Now we have created procedure which returns record set of all employee from EmployeeList table

Now its time to execute the stored procedure.

Expected output

Records from Employee List tables. Correct

Auctual Output

use tempDatabase

exec sp_tables

tempDatabase	dbo	EmployeeList		TABLE	NULL
tempDatabase	INFORMATION_SCHEMA	CHECK_CONSTRAINTS		VIEW	NULL
tempDatabase	INFORMATION_SCHEMA	COLUMN_DOMAIN_USAGE		VIEW	NULL

The output is something different from what we have expected, so where we go wrong???

The finding is, if user defined stored procedure name is same as system defined stored procedure, then the DB engine search the SP from Master database not from the user defined database.

So, the procedure will be executed from "MASTER" database... Not from user database. In our case the stored procedure is System stored procedure name, So, its searched and executed from MASTER database itself.

Example 2

Lets create a stored procedure in TempDatabase with the name sp_Tables1, which is not a system defined stored procedure name.

use tempDatabase

go

CREATE PROCEDURE sp_tables1

AS

BEGIN

      select * from EmployeeList

END

GO

Now its time to execute the stored procedure.

Expected output

Records from Employee List tables. Correct

Auctual Output

use tempDatabase

exec sp_tables1

id          empName

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

1           Aakhya

2           Kruthika

3           Aanandita

4           Chandanika

5           Ceitra

6           Arasi

7           Siddhiksha

8           Sakshi

(8 row(s) affected)

It results as the same, what we expected... How and why?, The stored procedure name was prefixed as 'Sp_' , Then it should be searched in "MASTER" database. correct?

NO The reason is, This is not a "SYSTEM STORED PROCEDURE", So, It will be searched in Local database first and then "MASTER" database, In our case, The procedure will be there in user database (tempDatabase) itself, So, Its searched and executed from User database itself...

Example 3

I am creating one stored procedure in "MASTER" database with name Sp_Tables1. Before that we first need to drop the procedure sp_table1 from user database (tempDatabase) which we had created in previous example.

use tempDatabase

drop procedure sp_tables1

use master

go

CREATE PROCEDURE sp_tables1

AS

BEGIN

      select * from EmployeeList

END

GO

Now its time to execute the stored procedure. We are executing this procedure from user database (tempDatabase)

use tempDatabase

exec sp_tables1

Expected output

Don’t Know, because no procedure with sp_tables1 is present in user database(tempDatabase).

Auctual Output

use tempDatabase

exec sp_tables1

id          empName

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

1           Aakhya

2           Kruthika

3           Aanandita

4           Chandanika

5           Ceitra

6           Arasi

7           Siddhiksha

8           Sakshi

(8 row(s) affected)

The reason is, The procedure is NOT A SYSTEM STORED PROCEDURE..., So, The it searches the Stored procedure in Local database first. If it is not there in local then only it searches in MASTER database.

In our case, the procedure will be there in MASTER database. So, It is searched in use database first, But the procedure will not be there in user database, So, Its searched and executed from MASTER database.

Example 4

Now lets create one stored procedure in both MASTER and User database (tempDatabase) named Sp_Tables1.

IN MASTER DATABASE

use master

go

CREATE PROCEDURE sp_tables1

AS

BEGIN

      print 'from master'

END

GO

Command(s) completed successfully.

IN USER DATABASE

use tempDatabase

go

CREATE PROCEDURE sp_tables1

AS

BEGIN

      select * from EmployeeList

END

GO

Command(s) completed successfully.

Now its time to execute the stored procedure. We will executing this procedure from user database (tempDatabase)

use tempDatabase

exec sp_tables1

id          empName

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

1           Aakhya

2           Kruthika

3           Aanandita

4           Chandanika

5           Ceitra

6           Arasi

7           Siddhiksha

8           Sakshi

(8 row(s) affected)

The reason is, the procedure is NOT A SYSTEM STORED PROCEDURE..., So, the search operation is performed in user database (TempDatabase) first and then MASTER database.

If Procedure is there in user database then, the procedure will be executed from user database itself, and it will not be search in MASTER database.

Conclusion:

The 'SP_' prefixed stored procedure will not be searched the stored procedure in MASTER database always. It will be searched based on the various scenario described above.

When Object name is SYSTEM DEFINED NAME then only it searches the procedure in MASTER database first and then user current database.

Now it’s on u whether to use SP_ prefix to user defined stored procedure or not…

But DB practice says, not to use SP_ prefix to user procedure.

Coding gives immense pleasure J

Post Reference: Vikram Aristocratic Elfin Share