Stored Procedure in Microsoft SQL Server:

Stored Procedure is one of my favorite database objects in Microsoft SQL Server. In Data Processing Continuum, SP is more near to Data then view or batch. Moving nearer to data for good performance and data integrity is a rule of thumb in SQL Server. Creating your logic in Stored Procedure is not only improving performance and integrity but it reduce the network traffic as well because you are not making logic in client application but making it in SQL Server itself.

In short, stored procedure is nothing more than pre-compiled version of T-SQL statement under one name and that name could be re-used as when and when it is needed. There are mainly two types of stored procedure available System Stored Procedure and User Stored Procedure. System Stored procedure ships with Microsoft SQL Server and you can use it directly but for your customize need. You need to create your own user stored procedure. If you wish to create your own system stored procedure in-order to use it in all available databases in SQL Server instance, you can do it. I wrote one article for creating system stored procedure at:

http://www.sqlhub.com/2009/03/create-your-own-system-stored-procedure.html

One good example to write start up stored procedure at:

http://www.sqlhub.com/2009/03/startup-stored-procedure-sql-server.html

Anyway, let us now focus on our core topic of the article.

Benefits

--SP are compiled object so it is anyway fastest than batch or T-SQL query.

--SP reduces network traffic

--Provide consistency as you can use one stored procedure at many places in client application and at the time of modification all you need to do is, change at one place and you will done.

--provide abstraction layer and security as it hides the table name.

Why is it boosting up the speed?

Answer is quite simple as I described earlier is, it used to compile the Stored Procedure at first run, creates query plan for that and store it in memory so than repeated use of same stored procedure can be freed out from the burden of creating query plan. You can see stored query plan with following query in SSMS.

Note: below query will show you all compiled plan stored in system, not only those who are stored via stored procedures only.

select * from master.dbo.syscacheobjects

Be aware that, all the plans for stored procedure will not stay forever in syscacheobjects. It will obsolete when any index, on the table we have used in SP, is created/ dropped or big data manipulation performed on the table. However, you can mark your stored procedure as recompiled by following command in SSMS.

exec sp_recompile <<stored procedure name>>

There are mainly three commands to manage stored procedure. ALTER, CREATE and DROP.

Let us see it practically.

--create one table for demo

use adventureworks

CREATE TABLE emps

(

Name VARCHAR(50),

Dept VARCHAR(10),

Company VARCHAR(15)

)

--INSERT records

INSERT INTO emps

SELECT 'Ritesh','MIS','echem' UNION ALL

SELECT 'Bihag', 'MIS', 'CT' UNION ALL

SELECT 'Rajan', 'account','Marwadi' UNION ALL

SELECT 'Alka','account','tata' UNION ALL

SELECT 'Alpesh','Chemical','echem'

GO

--creating stored procedure

CREATE PROC listEmps

AS

SELECT * FROM emps

GO

--after creating SP,let us check whether it actually works!!!

EXEC listEmps

GO

--let us now alter stored procedure

ALTER PROC listEmps

AS

SELECT * FROM emps WHERE Name like 'R%'

GO

--let us check our modification

EXEC listEmps

GO

--finally when you are done with stored procedure

--drop it by:

DROP PROC listEmps
GO

I will provide you with more examples with complex structure of stored procedure very soon.

Reference: Ritesh Shah
http://WWW.SQLHub.Com
Note: Microsoft Book online is a default reference of all articles