Create your own system stored procedure - SQL-SERVER 2005

Create your own system stored procedure - SQL-SERVER 2005

Sometime, its great help if we can create our own stored procedure like any other system stored procedure. It will help us to access that stored procedure from any of the database within one sql-server instance though it have been created only once.

If you create any stored procedure in MASTER database with “sp_” prefix and mark it as system stored procedure than you can take an advantage of name resolution algorithm of engine. Don’t create any stored procedure with “sp_” prefix in your own database as it incur burden on engine, because when you try to execute stored procedure with “sp_” prefix, engine will first try to search it in MASTER database.

We will create one stored procedure in MASTER database.

CREATE PROC sp_list_of_Employees

AS

SELECT * FROM Employee

RETURN

I am assuming that you are having one table, name “Employee” in “AdventureWorks” database but not in “MASTER” database, though we have created above stored procedure in MASTER database.

Now, try to run above stored procedure.

USE MASTER
GO
exec sp_list_of_Employees

As soon as you will run this, you will be greeted with an error:

Msg 208, Level 16, State 1, Procedure sp_list_of_Employees, Line 4 Invalid object name 'Employee'.

If you have “Employee” table in “AdventureWorks” database then try to run procedure in AdventureWorks database.

USE AdventureWorks
GO
exec sp_list_of_Employees

Again you will be greeted with the same error:

Msg 208, Level 16, State 1, Procedure sp_list_of_Employees, Line 4 Invalid object name 'Employee'.

Though you ran the procedure from AdventureWorks database, but it is still looking at “MASTER” database for “Employee” table, because we have not marked it as system routines.

We can mark stored procedure with below given command:

USE MASTER

EXECUTE sp_ms_marksystemobject 'sp_list_of_Employees'

Now, try to run SP in AdventureWorks database and you will get results or you can run this stored procedure in any of the database in your SQL instance, which have Employee table.

USE AdventureWorks
GO
exec sp_list_of_Employees

Hope you have enjoyed reading.

Happy SQLing!!!!

Reference: Ritesh Shah