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