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

Advertisements

4 Responses to “Create your own system stored procedure – SQL-SERVER 2005”

  1. Bihag Says:

    Ritesh,This is really good one. This is something new to me which I have never come across in any SQL Server article. Good! Keep your eyes moving around such topics.

  2. Bihag Says:

    Ritesh,This is really good one. This is something new to me which I have never come across in any SQL Server article. Good! Keep your eyes moving around such topics.

  3. Ritesh Shah Says:

    Thanks for you comment, Bihag…..

  4. Ritesh Shah Says:

    Thanks for you comment, Bihag…..


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: