Support us .Net Basics C# SQL ASP.NET ADO.NET MVC Slides C# Programs Subscribe Buy DVD

Stored procedures - Part 18

A stored procedure is group of T-SQL (Transact SQL) statements. If you have a situation, where you write the same query over and over again, you can save that specific query as a stored procedure and call it just by it's name.

There are several advantages of using stored procedures, which we will discuss in a later video session. In this session, we will learn how to create, execute, change and delete stored procedures.










Creating a simple stored procedure without any parameters: This stored procedure, retrieves Name and Gender of all the employees. To create a stored procedure we use, CREATE PROCEDURE or CREATE PROC statement.

Create Procedure spGetEmployees
as
Begin
  Select Name, Gender from tblEmployee
End

Note: When naming user defined stored procedures, Microsoft recommends not to use "sp_" as a prefix. All system stored procedures, are prefixed with "sp_". This avoids any ambiguity between user defined and system stored procedures and any conflicts, with some future system procedure.

To execute the stored procedure, you can just type the procedure name and press F5, or use EXEC or EXECUTE keywords followed by the procedure name as shown below.
1. spGetEmployees
2. EXEC spGetEmployees
3. Execute spGetEmployees

Note: You can also right click on the procedure name, in object explorer in SQL Server Management Studio and select EXECUTE STORED PROCEDURE.

Creating a stored procedure with input parameters: This SP, accepts GENDER and DEPARTMENTID parameters. Parameters and variables have an @ prefix in their name.

Create Procedure spGetEmployeesByGenderAndDepartment 

@Gender nvarchar(50),
@DepartmentId int
as
Begin
  Select Name, Gender from tblEmployee Where Gender = @Gender and DepartmentId = @DepartmentId
End

To invoke this procedure, we need to pass the value for @Gender and @DepartmentId parameters. If you don't specify the name of the parameters, you have to first pass value for @Gender parameter and then for @DepartmentId.
EXECUTE spGetEmployeesByGenderAndDepartment 'Male', 1

On the other hand, if you change the order, you will get an error stating "Error converting data type varchar to int." This is because, the value of "Male" is passed into @DepartmentId parameter. Since @DepartmentId is an integer, we get the type conversion error.
spGetEmployeesByGenderAndDepartment 1, 'Male'

When you specify the names of the parameters when executing the stored procedure the order doesn't matter.
EXECUTE spGetEmployeesByGenderAndDepartment @DepartmentId=1, @Gender = 'Male'

To view the text, of the stored procedure
1. Use system stored procedure sp_helptext 'SPName'
OR
2. Right Click the SP in Object explorer -> Scrip Procedure as -> Create To -> New Query Editor Window

To change the stored procedure, use ALTER PROCEDURE statement:
Alter Procedure spGetEmployeesByGenderAndDepartment 
@Gender nvarchar(50),
@DepartmentId int
as
Begin
  Select Name, Gender from tblEmployee Where Gender = @Gender and DepartmentId = @DepartmentId order by Name
End

To encrypt the text of the SP, use WITH ENCRYPTION option. Once, encrypted, you cannot view the text of the procedure, using sp_helptext system stored procedure. There are ways to obtain the original text, which we will talk about in a later session.
Alter Procedure spGetEmployeesByGenderAndDepartment 
@Gender nvarchar(50),
@DepartmentId int
WITH ENCRYPTION
as
Begin
  Select Name, Gender from tblEmployee Where Gender = @Gender and DepartmentId = @DepartmentId
End

To delete the SP, use DROP PROC 'SPName' or DROP PROCEDURE 'SPName'

In the next seesion, we will learn creating stored procedures with OUTPUT parameters.

18 comments:

  1. Thanks for this very detailed explanations and samples. keep up the good work.

    ReplyDelete
  2. I have a question . How to decrypt an stored procedure . Once you have encrypted there is no way to view the code .

    Thanks !!!!
    wasim.add@gmail.com

    ReplyDelete
    Replies
    1. Alter the procedure removing the encryption keyword as below

      Alter Procedure spGetEmployeesByGenderAndDepartment
      @Gender nvarchar(50),
      @DepartmentId int

      as
      Begin
      Select Name, Gender from tblEmployee Where Gender = @Gender and DepartmentId = @DepartmentId
      End

      Delete
  3. I faced One Interview in that i got a Questions on stored procedure that if i have a table tblEmp Where Column name Are Name And Age as given below so i want to create a stored procedure that when u execute that procedure it will show the whole table but second row column value of age should be 30 and it should not update permanently in my table as if i will use select command then it will show my original table so how can i create a procedure like that???????????????????

    Name Age
    ------- ------
    Mike 45
    Jame 40
    Todd 35

    ReplyDelete
    Replies
    1. create proc spShowEmpDetails
      as
      begin

      select [Name], Age=30 from tblEmp
      end

      Delete
    2. Thanks Himanshu, I did not know this query syntax.

      Delete
    3. Himanshu, I want to change only 2nd row means Jame age should be 30
      only when we will run our proc.

      Delete
    4. CREATE PROC TRAIL
      @name varchar(10),
      @age int
      AS
      BEGIN

      update Employee set Age=@age where Name=@name

      SELECT Name,Age from Employee
      END

      execute TRAIL @age=30,@name='Jame'

      Delete
    5. CREATE PROC spShowEmpDetails
      AS
      BEGIN
      SELECT [Name], CASE WHEN ROW_NUMBER() OVER(ORDER BY Name) =2 THEN '30' ELSE Age END AS Age FROM tblEmp
      END

      Delete
    6. Great solution Sardar G Thats great and thanks alot.........

      Delete
    7. The above problem can be solved using following steps

      step-1

      Create the Table
      create table tblEmp(Name nvarchar(30), Age tinyint)

      step-2
      Insert Some record to it.

      insert into tblEmp
      select 'Mike', 45
      union all
      select 'Jame', 40
      union all
      select 'Todd', 35

      select * from tblEmp

      Step-3

      Create the stored procedure

      create procedure sp_ChangeAgeinSecondRow_tblEmp
      As
      Begin
      declare @employee table(Name nvarchar(40), Age tinyint);
      insert into @employee
      select name, age from tblEmp;
      update @employee set Age=30 where Name='jame';
      select Name, Age from @employee;
      End

      step-4
      execute the stored procedure

      execute sp_ChangeAgeinSecondRow_tblEmp

      select * from tblEmp

      Check the results and verify that your problem has solved.

      Delete
  4. Wow, thanks for such high quality sql training videos.

    ReplyDelete
  5. Hello I am kaushick.

    Surely Sir this is a great video and good learning material. i am little stuck on last point.
    finally you show us how to encrypt the store procedure. Would you please share
    how to decrypt store procedure detail. i have googled for some time and there are some scripts to decrypt (shared by some forums). But all are without any explanation.

    ReplyDelete
    Replies
    1. just alter the procedure by removing 'with encryption' string

      Delete
    2. Hi naveen murari
      seems this is not possibile by just removing 'with encryption'
      I am the user I didn't saved the sp script.
      I don't have the script of Storedprocedure.
      how can decript the sp.
      when I alter I get the error sp is encripted.

      Delete
  6. Absolutely fantastic! You are a life saver! Carry on the good work!

    ReplyDelete
  7. Hi sir Kindly make one video on Decryption of the Stored procedure .... please please please

    ReplyDelete

If you like this website, please share with your friends on facebook and Google+ and recommend us on google using the g+1 button on the top right hand corner.