Stored Procedure in SQL server (in depth)

A stored procedure in SQL Server is a group of one or more Transact-SQL statements that has been created and stored in the database. Stored procedure will accept input parameters.

Why we need to use Stored Procedure

Here I am taking a simple example. I have an application which is install in more than 1000 machine. Application has some functionality like Registration, sells, inventory and reports etc. For each functionality application interact with the database server. Just think if we are not creating database object which store the sql statement like select, insert update and delete statement then what happen. We need to write these statements in application code itself.

For Each request they send the set of T-Sql statement.

There are following disadvantage when we keep the code on client machine

Ø  Network traffic is high – suppose sql statement is having 1000 line of code then each request we need to send number of bits through network to connect the database server and get the response from server. This is for one client machine then thinks about no of client machine.

Ø  Maintenance of the code is difficult – we are keeping the code at client side then think if we need to do some changes on the sql statement we need to update this code on every machine. Again we need to install this on every machine. It is very tedious task.

This code deploy on the every client machine. The above sql statement will return top 10 records. But now suppose our requirement will be changed and now we want to display the top 15 records then again we need to changes the sql statement

And we need to ageing deploy it in all machines. It is very tedious task.

Ø  No Security for the code I means it will be easily hacked – Code at the client machine. It can view and easily hacked. Then can change the code because they know the structure the table. In place of select they can write the insert or update statement.

So there is no security.

Ø  Performance of the code is slow: For each sql statement every time sql server will create the execution plan for executing the sql script. So it will be the slow.

To over coming this issue we need to keep the T-sql script on the central location or we need to store it on the database server itself.

To keep the T-Sql script at the server side we have following option

Ø  View

Ø  Stored Procedure

Ø  Function

               

View and function we will see the next post.

Stored Procedure

A stored procedure is nothing more than prepared SQL code that we save in the database server as an object so we can reuse the code over and over again.  Just call the stored procedure to execute the SQL code that you saved as part of the stored procedure.

There are two type of stored procedure

Ø  User defined Stored Procedure

Ø  System Stored procedure

User Define Stored Procedure

A Stored Procedure (SP) which is created by the developer is knows as User define Stored Procedure

Syntax

                                  

 See the example

CREATE PROCEDURE Usp_GetEmpDetails (                 --Input parameters                 @EmpId varchar(10),                 --Output parameters                 @Name varchar(100) OUT ) AS BEGIN SELECT [EmployeeKey]       ,[FirstName]       ,[LastName]       ,[MiddleName]       ,[HireDate]       ,[BirthDate]       ,[MaritalStatus]       ,[Gender]       ,[StartDate]       ,[EndDate]       ,[Status]   FROM [AdventureWorksDW2008R2].[dbo].[DimEmployee]   where EmployeeKey =@EmpId select @Name= (select FirstName FROM [AdventureWorksDW2008R2].[dbo].[DimEmployee]                                                                 where EmployeeKey =@EmpId) Print @Name END GO

Now Press F5 to create the SP.

We see this sp on below

  

For executing the Sp

EXEC or Execute

Now see the Output parameter

We can execute it by GUI

Right Click on SP name and select Execute Stored Procedure. We will get the new window in that we need to supply the Input parameter.

Click ok

If you want to make the changes on the SP we need to User ALTER

Execute it or Press F5 the changes will be reflecting.

Suppose if you want to see the content of SP in future then there is two way to see

1.       Using GUI

Right click on the SP

And Click on the Modify.

We will get the SP content

2.        2^nd way to get the content of the SP is using System Stored procedure

SP_helptext <Sp name> SP_helptext 'Usp_GetEmpDetails'

Creating SP with default parameter

When we declaring the Input parameter in SP and if we assign the values of that input parameter that is treated as default parameter. See below

CREATE PROCEDURE [dbo].[Usp_GetEmpDetails_DefaultPara] (                              @EmpId varchar(10)=1 ) AS BEGIN SELECT [EmployeeKey]       ,[FirstName]       ,[LastName]       ,[MiddleName]       ,[HireDate]       ,[BirthDate]       ,[MaritalStatus]       ,[Gender]       ,[StartDate]       ,[EndDate]       ,[Status]   FROM [AdventureWorksDW2008R2].[dbo].[DimEmployee]   where EmployeeKey =@EmpId END

Now Executing this SP

Exec Usp_GetEmpDetails_DefaultPara 1 EXEC Usp_GetEmpDetails_DefaultPara   --by default it will take the Input parameter value is 1

  

When we execute the SP if we are not supplying the value it will take the default value.

Using Return in Stored procedure

If the Stored procedure is executed successfully then it will return the 0 (zero) else it returns non-zero.

See the example

Create PROCEDURE [dbo].[Usp_GetEmpDetails_Return] (                              @EmpId varchar(10)=1,                 @Result varchar(100) OUT ) AS BEGIN declare @Rec_count int; select @Rec_count=Count(*) FROM [AdventureWorksDW2008R2].[dbo].[DimEmployee]   where EmployeeKey =@EmpId  IF @Rec_count=0  Begin  Print 'Employee doesn''t Exist'  return 10  End  Else  Begin   SELECT [EmployeeKey],[FirstName],[LastName],[MiddleName],[HireDate],[BirthDate],                    [MaritalStatus],[Gender],[StartDate],[EndDate],[Status]   FROM [AdventureWorksDW2008R2].[dbo].[DimEmployee]   where EmployeeKey =@EmpId   End END

Here I am taking the simple example in this SP I am passing the EmpID and checking that EmpID is exist or not. If the EmpID is not exist the returning 10 else displaying the EmpID details

Now I am executing the SP

I am supplying the EmpID=10. See the result below.

Now I am going to supply EmpID which doesn’t exist on the DimEmployee table

See the Message and also see the Return value.

Recursive Stored procedure

A stored procedure can call itself. If a Stored procedure calls itself then we called this SP is recursive stored procedure. We can call up to 32 nested level of the SP.

Here I am taking simple example. In this example I am creating a factorial

CREATE PROCEDURE Factorial (     @Number Integer,     @Return Varchar(100) OUTPUT ) AS     DECLARE @In Integer     DECLARE @Out Integer     IF @Number != 1         BEGIN         Set @In = @Number - 1         EXEC Factorial @In, @Out OUTPUT         SET @Return = @Number * @Out     END         ELSE             BEGIN                 SELECT @Return = 1             END RETURN @Return GO

Now I am executing this SP

declare @x int Exec @x= Factorial  5,@x OUTPUT Print 'Factorial Value : ' + Cast(@x as varchar(20))

Benefit of Stored procedure              

Ø  Execution plan reused

Ø  Reduce the Network traffic

Sp reduce the network traffic because see the below Stored procedures are stored in the database server. Client sends the SP name as well as parameter only.

Ø  Better security

We can provide the security to the Sp

Ø  Reusable

Ø  Avoid the SQL injection attack

Some points regarding SP

Ø  Sp can return Zero, single or multiple values.

Ø  We can use Transaction

Ø  We can use Try Catch block (Error Handling)

Ø  We can call Function or SP

Ø  We can use DML,DDL,TCl command

Ø  We can’t use SP as a select or Where ( select Exec Factorial 5)

Ø  SP can have up to 21,000 input parameter

Ø  Don’t start the SP name with SP_ Prefix because if System SP having the same prefix. If we use this prefix then it will be slow because when we execute this SP first it will go to the system Stored Procedure and search there. So avoid this prefix.

System Stored Procedure

 System Stored Procedures are useful in performing administrative and informational activities in SQL Server. Most commonly used system Stored Procedures are described below.

We can find it on the below location

List of some Important Stored Procedure

Ø  SP_Help : it will give the information of each database object(table, view, function etc) which are  stored in the current Database

Ø  SP_Helpdb :  it will give the information of all database which are in the database server.

Ø  SP_HelpText : it will return the text of the Database object (View and SP)

Ø  SP_Spaceused : it will return the size of current database

Ø  Sp_who : Provides information about current users, sessions, and processes in an instance of the Microsoft SQL Server Database Engine. The information can be filtered to return only those processes that are not idle, that belong to a specific user, or that belong to a specific session

Ø  SP_table : Return the list of database object which are stored in the current database.

  

Ø  SP_Columns <table_name> :

Example :  SP_Columns ‘FactFinance’

It returns the list of the column name with all details of the supplied table.

 

For more details: Read System Stored Procedure