Friday 24 June 2016

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.        2nd 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.
 




10 comments:

  1. I am very glad to read your informative blog. Thanks a lot for your valuable sharing
    you can also visit here ms sql corporate training

    ReplyDelete
  2. It is very good blog and useful for students and developer ,

    Sql server DBA Online Course Bangalore

    ReplyDelete
  3. It's Very informative blog and useful article thank you for sharing with us , keep posting learn more about BI Tools
    Tableau Online Training

    ReplyDelete
  4. Thanks you for sharing the article. The data that you provided in the blog is infromative and effectve. Through you blog I gained so much knowledge. Also check my collection at MSBI online course Bangalore Blog

    ReplyDelete
  5. This comment has been removed by the author.

    ReplyDelete
  6. Appreciating the persistence you put into your blog and detailed information you provide.Surya Informatics

    ReplyDelete
  7. Through this post, I know that your good knowledge in playing with all the pieces was very helpful. I notify that this is the first place where I find issues I've been searching for. You have a clever yet attractive way of writing on Msbi online training
    Msbi online training Hyderabad
    Msbi online training India
    Msbi online course
    Msbi course
    Msbi training
    Msbi certification training

    ReplyDelete

If you have any doubt, please let me know.

Popular Posts