Home » SQL Server Stored Procedure

SQL Server Stored Procedure

A stored procedure is a group of SQL statements compiled into one unit for later use and can be used many times. So, whenever you need to execute the query, instead of calling it you can just call the stored procedure. It is stored in memory.




Stored procedures are parsed and optimized as soon as they are created.

There are three types of parameters that can be used with stored procedure as given below .

  1. IN: This is the Default Parameter for the procedure. It always receives the values from the users.
  2. OUT:  This parameter always returns the values to the user .
  3. IN OUT: This parameter performs both the operations. It receives value from as well as sends the values to the user.

Syntax 

Create Stored procedure with input parameter 

CREATE PROC | PROCEDURE procedurename  (
 @parameter1 AS DATATYPE, @parameter2 AS DATATYPE
)
AS
BEGIN
sql statement ...
END

Create Stored procedure with an output parameter

CREATE PROC | PROCEDURE procedurename(  
@parameter1 AS DATATYPE, @parameter2 AS DATATYPE ) 
AS 
BEGIN
 sql statement ... 
END

Execute stored procedure

EXECUTE | EXEC  stored_procedurename  parameter1, parameter2, ...

Lets look at an example of SQL SERVER stored procedure .

First we creates a table named as employee and insert some records into this table.

CREATE TABLE EMPLOYEE (
EMPID INT NOT NULL PRIMARY KEY, 
EMPNAME VARCHAR(50) NOT NULL, 
EMPCODE VARCHAR(15))

INSERT INTO EMPLOYEE(EMPID, EMPNAME, EMPCODE)
VALUES (501, 'RAJESH MITAL', 'XCV567'),
(502, 'SUJOY SINGH', 'XCB787'),
(503, 'ABHISHEK MISHRA', 'FJV542'),
(504, 'HARSHIT KAPOOR','NMD557'),
(505, 'MANISH RAWAT', 'QJB987'),
(506, 'DEEPAK KUMAR', 'FZV704'),
(507, 'HARSH KUMAWAT','NET657'),
(508, 'ROZER JR', 'DDB987'),
(509, 'BALAJI NEGI', 'KL0542'),
(510, 'NARAYANA SWAMI','NSD447'),
(511, 'LOKESH MANTRI', 'BBB007'),
(512, 'SUMIT VIYAS', 'FFNM04'),
(513, 'BABA NAYAK','NEG157')

Stored Procedure without parameter

Following stored procedure returns a employee details, when it is executed. It does not accept any parameter.
CREATE PROC USP_FetchEmployeeDetails
AS
BEGIN
SELECT * FROM EMPLOYEE ;
END
It is a very simple form of stored procedure with out parameter, It returns a employee details .
To execute the stored procedure , use execute command following by stored procedure name as given below.

EXEC USP_FetchEmployeeDetails 

As you can see, It returns a employee details.

 

Stored procedure with input parameter 

Lets create a stored procedure with input parameters that returns the employee details based on parameter values .

Following stored procedure accepts one parameter value as EMPID and returns a employee details for that value from employee table.

CREATE PROC USP_FetchEmployeeInfo
( @empId AS INT)
AS
BEGIN
SELECT * FROM EMPLOYEE WHERE EMPID =@empId ;
END

Lets execute the stored procedure to see a employee details for EMPID=513.
To get the details for EMPID = 513 , you have to pass a value for parameter @empId that is 513 .
EXECUTE USP_FetchEmployeeInfo 513

As you can see, It returns employee details only for EMPID =513.

Modifying an existing stored procedure

You can also modify an existing stored procedure using following syntax.

ALTER PROC| PROCEDURE procedure_name (@parameter datatype,.. )
BEGIN
Sql statement 
END

Lets modify the above stored procedure – USP_FetchEmployeeInfo

As of now Stored Procedure is returning all columns from employee table so to make it to return details only for specific columns that is empId and EmpName , we use alter command to modify the Store Procedure.

ALTER PROC USP_FetchEmployeeInfo
( @empId AS INT)
AS
BEGIN
SELECT EMPID, EMPNAME FROM EMPLOYEE WHERE EMPID =@empId ;
END

Once you modify stored procedure, you need to execute stored procedure scripts again in database to reflect the changes other wise it will not return data as per new changes.

Now execute stored procedure to see, whether it returns data as per the new changes .

EXECUTE USP_FetchEmployeeInfo 513

As you can see, this time it returns only two columns (EMPID and EMPNAME) for employee details.

Stored procedure with an Output parameter

Following stored procedure have an output parameter that returns total employee count in employee_details  table.

CREATE PROC get_EmpCount ( 
@EmpCount AS INT OUTPUT ) 
AS
BEGIN
SELECT count(1) AS CNT FROM EMPLOYEE;
END




To execute the stored procedure having output parameter is somthing different as given bleow.

DELCARE @count AS INT
EXEC get_EmpCount @count OUTPUT

Following are the step to exeute stored procedure with OUTPUT parameter:

  1. First, initialize a variable of same data type as that of the output parameter. Here, we have declared a variable @count of type integer.
  2. Pass the @count variable to the stored procedure. You have to specify an output  keyword. If you don’t specify the output keyword, the variable will be null.
  3. Then execute the stored procedure.

As you can see, Stored procedure returns total the total counts of employee as an output.

Stored procedure with Input and Output parameter 

Following stored procedure uses both input parameter as well as output parameter.

It returns a total count of employee based on namvalue passed in @EMPName parameter.

   CREATE PROC fetch_EmpCount_basedonNameChars ( 
   @EmpName AS VARCHAR(50), @EmpCount AS INT OUTPUT ) 
   AS
   BEGIN 
   SELECT COUNT(*) AS CNT FROM EMPLOYEE WHERE EmpName like '%'+@EmpName+'%' 
   END

 

Lets execute the stored procedure, here we have passed parameter value ‘KU’, so this stored procedure will return the count of total number of employee those are having ‘KU’ in their name.

DECLARE @count AS INT
EXEC fetch_EmpCount_basedonNameChars 'KU', @count OUTPUT

You can also execute it one more way as given below.

DECLARE @count AS INT
EXEC fetch_EmpCount_basedonNameChars @EmpName ='KU', @EmpCount =@count OUTPUT

Here we are using parameter name then passing a value to it , so using the parameter names  gives you a facility to pass the parameters in any order else you have to pass the value for parameter  in same order as you have defined in stored procedure.

Encrypting a Stored Procedure

You can also Encrypt a Stored Procedure, If you want to hide stored procedure logic from end user.

You can refer a post How to Encrypt SQL Server Stored Procedure




Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.