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

 

Reference: Ritesh Shah

Advertisements

One Response to “Create your own system stored procedure – SQL-SERVER 2005”

  1. Edward Joell Says:

    I am curious about the rights needed to run a system stored procedure. Does you user need to be explicitly granted execute rights on that stored procedure? I ask because it came up for me with a system stored procedure I created. The user I was running as had db_owner rights in the database context from which it was calling the sp but had no rights in master. The error was thrown that my user did not execute rights on the stored procedure. Additionally, I MSDN mentioned that system stored procedures exist virtually in each database as part of the sys schema. But mine was not so listed.


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: