RSS

Steps for Creating System Stored Procedure In SQL Server 2008 R2

03 Oct

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.

SQL Server 2008 R2

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

SQL Server Management Studio

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

Select Database

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

New Stored Procedure

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

Creating System Stored Procedure

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.

Create Procedure Basic Template

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

Stored Procedure Script

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.

Template Parameter Values

Close the script and save it, if required.

SQL Query

Creating SQL Server System Stored Procedure

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

New Query

Type in the following query for creating a stored procedure:

Creating Procedure

Select the query and click on Execute to create it.

Execute Query

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

Referesh Stored Procedure

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

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:

SIMPLE_PROC

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

EXECUTE SIMPLE_PROC

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

Check Procedure
Command 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.

 

Tags:

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