Steps for Creating System Stored Procedure In SQL Server 2008 R2

In our previous discussion, we have discussed about transaction log corruption and backup in SQL Server. Now, in this article we will know the steps for creating system stored procedure in SQL Server 2008 R2.

Organizations where SQL Server system is used for database storage and maintenance, projects with 1000s of database tables and stored procedures are common to find. Adding even a single parameter to an existing stored procedure can straightaway put an impact on a large number of other stored procedures or database tables as they are called from them. Moreover, it is in most cases possible that a reliable result in not generated while trying to identify the impacted procedure out of all the rest. Nevertheless, SQL is designed to store database information within system tables and with those system tables; anything can be queried that is associated with the database.

SQL Server by default comes with a variety of stored procedures defined that further help in managing objects on the platform. This post will be briefing about the number of system-stored procedures in SQL and detailing about how there use can be replaced with self-generated SQL queries.

The Advantage of creating system stored procedure in SQL Server

Generating SQL queries on your own gives greater control to the user over the output. A user if well aware about the system tables of a database, any situation can be managed with the help of self-generated scripts. Moreover, you are not required to remember those various stored procedures for getting things done on the server. On SQL Server, system stored procedures can be found within the master database. Generally, the names of these begin with ‘sp’ and use an ‘_’ as a separator along with the name of the procedure as ‘’. However, when creating your own stored procedures it is good to not to start with ‘sp_’.

An example explaining the creation of system stored procedures has been shared in the following part of the post along with illustrating images, for better understanding.

Basic Steps to Create System Stored Procedure in SQL Server 2008 R2

Run SQL Server and connect to it.

Browse and select the preferred database from the list of managed database on the server.

Select and expand the preferred database for which you want to create stored procedure.

Now, expand ‘Programmability’ and right click on Stored Procedures. Select ‘New Stored Procedure…’ to create your own script for a custom stored procedure.

Doing this will create a framework for designing your own stored procedure which will look like the image given below:

Another way of generating custom stored procedure is by selecting a Stored Procedure Template from the Template Explorer. Drag and drop this template on the TSQL interface.

The script generated will look like the following, which creates a basic query for creating a Stored Procedure.

Now click on Query from the menu bar and select Specify Values for Template Parameters. This will let you create parameters for the templates and provide values for the schema.

Close the script and save it, if required.

Creating SQL Server System Stored Procedure

Click on New Query to write your own stored procedure accordingly.

Type in the following query for creating a stored procedure:

Select the query and click on Execute to create it.

Now right click on Stored Procedure and click on Refresh to refresh the list of stored procedures and make yours visible.

You will find your Stored Procedure Created by its name, i.e. in this case it would be – dbp.SIMPLE_PROC

Now to check whether it’s functioning as planned, you can execute the Stored Procedure. To do so, execute the following script:

Select the query and click on Execute from the top to run the query.

You will receive the results, i.e. the defined table displayed along with the message ‘Command(s) Completed Successfully’

These are the basic steps that one can follow for creating their own system Stored Procedures on SQL Server. If you are keen on creating your own SQL Server system Stored Procedure too, following these steps will help achieve desired results. The stored procedure explained in this example is just for sample purpose. You can create a more detailed stored procedure as per your requirement.