User defined stored procedure should not be Prefixed with "SP_". Why and When ?

PandianS
Posted by in Sql Server category on for Beginner level | Points: 150 | Views : 13449 red flag
Rating: 5 out of 5  
 1 vote(s)

According to Microsoft best practice, User defined stored procedures should not be started with 'SP_'. The reasone is...

Introduction

According to Microsoft best practice, User defined stored procedures should not be prefixed with 'SP_'.

Why and When ?


I would
like to have the following various scenarios...



Scenario: 1

I am creating one stored
procedure in (DotnetFunda) database named : Sp_Tables

Creating stored
Procedure:

USE DotNetFunda
GO
CREATE
 PROC 
Sp_Tables
AS
BEGIN
SET NOCOUNT 
ON
SELECT 'This Procedure is executed from "DotNetFunda" Database' 'Status'
 
END
GO


Executing the stored
Procedure:

USE DotNetFunda 
GO
EXEC
 
Sp_Tables


The stored
Procedure executed from "DotNetFunda" database, so it should return the result as given below. Correct ?




But
, It results as given below instead...

Result:



The reason
is, If the stored procedure which we have named is "SYSTEM STORED PROCEDURE" then only the it searches the procedure in "MASTER" database first,

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.

Scenario: 2

I am creating one stored
procedure in (DotnetFunda) database named : Sp_Tables1

Creating stored
Procedure:

USE DotNetFunda 
GO
CREATE
 PROC
 Sp_Tables1
AS
BEGIN
SET NOCOUNT 
ON
SELECT 'This Procedure is executed from "DotNetFunda" Database' 'Status'
 
END
GO


Executing the stored
Procedure:

USE DotNetFunda 
GO
EXEC
 Sp_Tables1


The stored
Procedure executed from "DotNetFunda" database, so it should return the result as given below. Correct ?



Yes
, 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
.

Result:



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 itself, So, Its searched and executed from User database itself...

Scenario:
3

I am creating one stored
procedure in "MASTER" database only named : Sp_Tables1, insteadof User database.

Creating stored
Procedure:

USE MASTER
GO
CREATE
 PROC
 Sp_Tables1
AS
BEGIN
SET NOCOUNT 
ON
SELECT 'This Procedure is executed from "Master" Database' 'Status'
 
END
GO


Executing the stored
Procedure:

USE DotNetFunda 
GO
EXEC
 Sp_Tables1


The stored
procedure "Sp_Tables1" is created in "MASTER" database, not in "DotnetFunda" database.

The stored
Procedure executed from "DotNetFunda" database, What will happen ?

Result:



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

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

Scenario:
4

I am creating one stored
procedure in both "MASTER" and "DotnetFunda" database(s) named : Sp_Tables1.

Creating stored
Procedure:

USE MASTER
GO
CREATE
 PROC
 Sp_Tables1
AS
BEGIN
SET NOCOUNT 
ON
SELECT 'This Procedure is executed from "Master" Database' 'Status'
 
END
GO

USE
 DotNetFunda 
GO
CREATE
 PROC
 Sp_Tables1
AS
BEGIN
SET NOCOUNT 
ON
SELECT 'This Procedure is executed from "DotnetFunda" Database' 'Status'
 
END
GO


The stored
procedure "Sp_Tables1" is created in "MASTER" and "DotnetFunda" database(s).

Executing the stored
Procedure:

USE DotNetFunda 
GO
EXEC
 Sp_Tables1


The stored
Procedure executed from "DotNetFunda" database, What will happen ?

Result:



The reason
is, The procedure is NOT A SYSTEM STORED PROCEDURE..., So, The it searches the Stored procedure in user database first and then MASTER database. 

If
it's there in user database then , The procedure will be executed from user database itself,

It will not be search the procedure 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 Object name / various scenario.

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

The various scenarios for better understanding only.

Anyway, we should follow the recommendations not Prefixing the stored procedure name as 'Sp_' even its not searching in MASTER database in all scenarios...

Page copy protected against web site content infringement by Copyscape

About the Author

PandianS
Full Name: Pandian Sathappan
Member Level: Silver
Member Status: Member,MVP
Member Since: 5/11/2009 2:24:28 AM
Country: India
Cheers www.SQLServerbuddy.blogspot.com iLink Multitech Solutions
http://sqlserverbuddy.blogspot.com/
Microsoft Certification - MCITP: Microsoft Certified IT Professional - SQL Server 2008 Database Administrator - MCTS: Microsoft Certified Technology Specialist - SQL Server 2005 - MCP: Microsoft Certified Professional - SQL Server 2000

Login to vote for this post.

Comments or Responses

Login to post response

Comment using Facebook(Author doesn't get notification)