SQL Server Mentalist

Hi Friends,

Today we are going to look on one of the important aspects of SQL Server i.e Stored Procedure

What is a Stored Procedure?

The name for a batch of Transact-SQL or CLR code that is stored within SQL Server and can be called directly by applications or within other programming constructs.

Types of Stored Procedures

There are two main types of stored procedure –

• system stored procedures and

• user-defined stored procedures.

We also have extended stored procedures that can reside as either system or user-defined types. Extended stored procedures give functionality that is not necessarily contained within SQL Server, like allowing DOS commands to run and working with e-mail. It is also possible to create your own extended stored procedures.

System Stored Procedures

Although there is an engine that runs much of SQL Server, a number of stored procedures surround this. These are called while working with Enterprise Manager, or through a query tool, such as QueryAnalyzer. These procedures are installed with SQL Server, and hence, we don’t have to create them.

Every time we add or modify a table, make a backup plan, or perform any other administrative function from within Enterprise Manager, we actually call a stored procedure specifically written to complete the desired action. These stored procedures are known as system stored procedures, which are functions that enhance the basic functionality of SQL Server itself, either by extending the functionality of an existing system stored procedure or by creating new functionality that enhances the basics of what is already there.

For example, the sp_who system stored procedure will list connections to SQL Server, including all the system running processes.

System stored procedures are prefixed by sp_, so it is not advisable to use sp_ for any of the stored procedures that we create, unless they form a part of our SQL Server installation. Creating a stored procedure prefixed with sp_ and placing it in the master database will make it available to any database without the need to prefix the stored procedure with the name of the database.

User Stored Procedures

A user stored procedure is any program that is stored and compiled within SQL Server (but not in the master database).
User stored procedures can be categorized into three distinct types –

• user stored procedures,

• triggers, and

• user defined functions.

Each of these types has its features, uses, limitations, and differences.

User defined stored procedures provide an interface to a set of processing without the need for the end user to know the structure of the tables, the business logic of the organization, or the mathematics involved, to produce the end result. They also provide a secure method, which along with other security measures, can result in a database where data is protected from malicious or accidental modifications.

A trigger is a stored procedure which fires when a specified table action takes place. You are also limited to certain code that you can place in a trigger, as you should not return any data from a trigger.

A user defined function is a stored procedure which can take parameters, but only return one item of information, either a scalar value or a table of data.

To create a stored procedure

1. In Object Explorer, connect to an instance of Database Engine and then expand that instance.

2. Expand Databases, expand the database in which the stored procedure belongs, and then expand Programmability.

3. Right-click Stored Procedures, and then click New Stored Procedure.

4. On the Query menu, click Specify Values for Template Parameters.

5. In the Specify Values for Template Parameters dialog box, the Value column contains suggested values for the parameters. Accept the values or replace them with new values, and then click OK.

6. In the query editor, replace the SELECT statement with the statements for your procedure.

7. To test the syntax, on the Query menu, click Parse.

8. To create the stored procedure, on the Query menu, click Execute.

9. To save the script, on the File menu, click Save. Accept the file name or replace it with a new name, and then click Save.

The creation process depends on what we want it to do – we can either build the whole procedure immediately, or build the T-SQL first (for more complex solutions), check its performance, and finally, place it in a stored procedure. Both ways require us to wrap the T-SQL code with the same CREATE PROCEDURE command.

At the time of creation, SQL Server takes our code and parses it for any syntactical errors. Column names and variables are checked for existence at compilation. Even if they don’t exist, any temporary tables created within the stored procedure will also pass the compilation stage. This is known as deferred name resolution. It can be an asset or a drawback, as we can create temporary tables that exist for the lifetime of the stored procedure execution only, which is desirable, but if we define a temporary table and get something wrong later on, such as a column name, then the compilation will not pick up this error.

While executing the CREATE PROCEDURE statement, errors will be reported. Once it is compiled, the details of the stored procedure are stored in three system tables in the concerned database:

sysobjects

This table contains a row for each object that is created within the database. If you want to track an object in the database, you can use this as the main root. Apart from the object name and type, this table also keeps track of the object’s owner and time of creation. This can be useful in cross-checking the contents of our source control system with our database.

sysdepends

This table stores dependency information about objects. For example, when a stored procedure, view, or trigger is created, there can be references to other tables, views, or procedures within it. These references are known as dependencies. If one of the dependent objects alters, we may need to recompile the stored procedure. For example, if we alter an index on a table, we should recompile every dependent object.

syscomments

This holds the original SQL definition statements of the stored procedure. It also holds details of views, rules, defaults, triggers, CHECK constraints, and DEFAULT constraints.

It is possible to interrogate these data. By looking for rows with a P in the sysobjects table, we can obtain all the stored procedures. From that, we can find out when the stored procedure was created, and also move to the syscomments table by using the ID to retrieve detailed information about the procedure itself, like, if it is encrypted or not.

These tables are safe only for interrogation, although details within them can always change between SQL Server releases. One of the best methods to ensure that the code stored in your source control system matches that within your database is to check if the dates and times match or are within a few seconds of each other.

Why Use Stored Procedures?

By using stored procedures, we can reduce the time a process can take, as stored procedures are compiled.

Another gain from using stored procedures is that they are much simpler to maintain, as compared to raw T-SQL code. Since the stored procedure is held centrally within the database, any required bug fix, upgrade, or modification can be completed centrally, thus reducing the downtime. If the code is embedded within a program, as in VB.NET, we have to not only change the code in one central place but also distribute it to every client running the software. Modification of web sites also brings in its own set of problems, and rolling out a new ASP.NET client can prove problematic. This can be settled by using stored procedures.

The code in a stored procedure is executed in a single batch of work. This means that it is not necessary to include a GO command while writing code for procedures. SQL Server will take any of those statements and implicitly execute them, as a batch.

Perhaps, the greatest reason to create a stored procedure rather than use inline T-SQL, is security.

Stored Procedure
A stored procedure is a set of one or more SQL statements that are stored together in database. To create a stored procedure use CREATE PROCEDURE statement. To use the stored procedure you send a request for it to be executed. When server receives the request, it executes the stored procedure.
Stored procedures assist in achieving a consistent implementation of logic across applications. The SQL statements and logic needed to perform a commonly performed task can be designed, coded, and tested once in a stored procedure. Each application needing to perform that task can then simply execute the stored procedure. Coding business logic into a single stored procedure also offers a single point of control for ensuring that business rules are correctly enforced.
Stored procedures can also improve performance. Many tasks are implemented as a series of SQL statements. Conditional logic applied to the results of the first SQL statements determines which subsequent SQL statements are executed. If these SQL statements and conditional logic are written into a stored procedure, they become part of a single execution plan on the server. The results do not have to be returned to the client to have the conditional logic applied; all of the work is done on the server.
There are some concepts of stored procedures.

• A stored procedure is one or more SQL statements that have been compiled and stored with database. A stored procedure can be started by application code on the client.
• Stored procedure can improve database performance because the SQL statements in each procedure are only compiled and optimized the first time they are executed. In contrast SQL statements that are sent from a client to the server have to be compiled and optimized every time there are executed.
• In addition to SELECT statement, a stored procedure can contain other SQL statements such as INSERT,UPDATE,DELETE. It also contain control-of-flow language.
• A trigger is a special type of procedure that executes when rows are inserted, updated or deleted from table.
• A user defined function(UDF) is a special type of procedure that can return a value or a table.

Hope this helps !!

For more interesting information on SQL we can also look into similar topics such as

· BISQL # 34 : What is Identity IN SQL {Identity Part–I}

· BISQL # 35 : Deep dive into Identity Functions in SQL {Identity Part–II}

· BISQL # 36 : Queries to Understand more on Identity aspect {Identity Part–III}

· BISQL # 37 :Checking, Seeding and Reseeding of Identity {Identity Part–IV}

· BISQL # 38 : Introduction To Function – {SQL Server Function Part – I }

 

Hope you will like this post on Stored Procedure.

If you really like reading my blog and understood at least few thing then please don’t forget to subscribe my blog.

If you want daily link and analysis or interesting link go to following website which will give @ your inbox please subscribe our following link resource blog: Link Resource Website

           For More information related to BI World visit my Mentalist Blog

                     Link Resource Blog >> Daily Interesting links

                    SQL Server Mentalist >> SQL Learning Blog

                    Business Intelligence Mentalist >> BI World

                                  Connect With me on

        | FaceBook |Twitter | LinkedIn| Google+ | WordPress | RSS |

                              Copyright © 2011 – 2012 Vishal Pawar