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.

33 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
    8. declare @employee table(Name nvarchar(40), Age tinyint);
      insert into @employee

      I did not get this part

      Delete
    9. @employee is table variable

      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
    3. Once the procedure is encrypted,there is no way you can decrypt it.

      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
  8. Hello Sir, could you please tell me a query to replace 01 where it is 11 in the this number (I3MRNKC170411605). I want to replace 01 in all the rows where I notice 11. Kindly advise if any query anybody.

    ReplyDelete
    Replies
    1. SELECT REPLACE('I3MRNKC170411605',11,'01') FROM

      Delete
  9. Kindly note that number is I3MRNKC170401605 and I want to replace 0 in place of 1 from right in all rows.

    ReplyDelete
  10. Create proc sptest2
    @age int
    as begin
    select name, age=@age from Person
    end


    (OR)

    Create procedure Sptest
    as begin
    select name,age=30 from Person
    end

    ReplyDelete
  11. i got a question about the clause

    could we remove the [begin] and [End] clause in stored procedure query ?

    cuz i saw in other cases , the query of stored procedure doesn't have {begin} , {end} .
    IT GOES LIKE THIS BELOW .

    CREATE PROCEDURE procedure_name
    AS
    sql_statement
    GO;

    ReplyDelete
  12. when the T-SQL statement is just then you don't need to write begin and end

    ReplyDelete
  13. TABLE
    ****************************************
    CREATE TABLE [dbo].[Account](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [FirstName] [nvarchar](50) NULL,
    [LastName] [nvarchar](50) NULL,
    [Mobile_no] [int] NULL,
    [Password] [nvarchar](100) NULL,
    [ConfirmPassword] [nvarchar](100) NULL,
    [Country] [nvarchar](20) NULL,
    [Updatedate] [nvarchar](20) NULL,
    [updatedby] [nvarchar](20) NULL,
    [sourcechannel] [nvarchar](50) NULL,
    [role] [nvarchar](50) NULL,
    [category] [nvarchar](50) NULL,
    [dateofbirth] [datetime] NULL,
    [gender] [nvarchar](20) NULL,
    [pan] [nvarchar](50) NULL,
    [aadhar] [nvarchar](50) NULL,
    [isactive] [int] NULL,
    [isdelete] [int] NULL
    )
    *****************Executed successfully***********************

    STORED PROCEDURE
    **************************************************

    CREATE PROCEDURE [dbo].[sp_store]
    (
    @FirstName nvarchar(50),
    @LastName nvarchar(50),
    @Mobile_no int,
    @Password nvarchar(100),
    @ConfirmPassword nvarchar(100),
    @Country nvarchar(20),
    @Updatedate nvarchar(20),
    @updatedby nvarchar(20),
    @sourcechannel nvarchar(50),
    @role nvarchar(50),
    @category nvarchar(50),
    @dateofbirth datetime,
    @gender nvarchar(20),
    @pan nvarchar(50),
    @aadhar nvarchar(50),
    @isactive int,
    @isdelete int,
    @StatementType nvarchar(20) = '')
    As
    Begin
    if @StatementType = 'Insert'
    Begin
    Insert into MyAccount
    (FirstName, LastName, Mobile_no, Password,ConfirmPassword,Country,
    Updatedate,updatedby,sourcechannel,role,category, dateofbirth, gender,
    pan,aadhar,isactive,isdelete)
    Values(@FirstName,@LastName, @Mobile_no, @Password,@ConfirmPassword,@Country,@Updatedate,@updatedby,@sourcechannel,@role,@category, @dateofbirth, @gender,
    @pan,@aadhar,@isactive,@isdelete)
    End
    if @StatementType = 'select'
    Begin
    Select * from MyAccount
    End

    if @StatementType = 'update'
    Begin
    Update MyAccount
    Set FirstName=@FirstName, LastName=@LastName, Mobile_no=@Mobile_no, Password=@Password,ConfirmPassword=@ConfirmPassword,Country=@Country,Updatedate=@Updatedate,updatedby=@updatedby,sourcechannel=@sourcechannel,role=@role,category=@category, dateofbirth=@dateofbirth, gender=@gender,
    pan=@pan,aadhar=@aadhar,isactive=@isactive,isdelete=@isdelete
    End
    if @StatementType = 'delete'
    Begin
    Delete from MyAccount
    where FirstName=@FirstName
    End
    End
    GO

    ************Executed successfully**********
    [sp_store]
    Error if executing store procedure although after passing firstname
    Msg 201, Level 16, State 4, Procedure sp_store, Line 0 [Batch Start Line 11]
    Procedure or function 'sp_store' expects parameter '@FirstName', which was not supplied.

    ReplyDelete
  14. Step-1
    create table data(name varchar(50),age int)

    insert into data values('Mike',45),('Jame',40),('todd',35)

    Step-2
    create proc tempshow
    as
    begin
    create table #temp(name varchar(50),age int)
    insert into #temp
    select name,age from data

    update #temp set age=30 where name='Jame'
    select * from #temp

    end
    step-3
    exec tempshow

    ReplyDelete
  15. can you provide some examples where i have multiple sql statement in a stored procedure?

    ReplyDelete
  16. Thanks for the great explanation.
    I am not getting null values from the following
    create procedure spGender_Status_tblPerson_withPara
    @Gender nvarchar(50)
    as
    begin
    select p.Name, p.Email, p.Age, g.gender from tblPerson p
    full join tblGender g
    on p.GenderID = g.ID
    where g.Gender = @Gender

    end

    exec spGender_Status_tblPerson_withPara 'Male' -- working
    exec spGender_Status_tblPerson_withPara 'feMale' -- working
    exec spGender_Status_tblPerson_withPara 'unknown' -- working

    exec spGender_Status_tblPerson_withPara 'null' -- null values are not showing up
    How to get the null values.

    ReplyDelete
    Replies





    1. create procedure spGender_Status_tblPerson_withPara
      @Gender nvarchar(50)
      as
      begin
      if(@Gender='NULL')
      select p.Name, p.Email, p.Age, g.gender from tblPerson p
      full join tblGender g
      on p.GenderID = g.ID
      where isnull(g.Gender,'')=''
      else
      select p.Name, p.Email, p.Age, g.gender from tblPerson p
      full join tblGender g
      on p.GenderID = g.ID
      where g.Gender = @Gender

      end

      Try this it will work

      Delete
  17. create table tblEmployee
    (Id int,Name varchar(50),Gender nvarchar(50),DepartmentId smallint)

    insert into tblEmployee(Id,Name,Gender,DepartmentId)values(1,'sam','male',1),(2,'Ram','male',1),(3,'sara','female',3),(4,'todd','male',2),(5,'john','male',3), (6,'sara','female',2),(7,'james','male',1),(8,'Rob','male',2),(9,'steve','male',1),(10,'pam','female',2)

    ReplyDelete

It would be great if you can help share these free resources