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.

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

CREATE PROC | PROCEDURE procedurename(  
@parameter1 AS DATATYPE, @parameter2 AS DATATYPE OUTPUT ) 
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

CREATE PROC USP_FetchEmployeeDetails
AS
BEGIN
SELECT * FROM EMPLOYEE ;
END

EXEC USP_FetchEmployeeDetails

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

EXECUTE USP_FetchEmployeeInfo 513

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

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 the details for specific columns that is EmpId and EmpName, we use alter command to modify the stored 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.

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

To execute the stored procedure having output parameter is something different as given below.

DELCARE @count AS INT
EXEC get_EmpCount @count OUTPUT

Following are the step to execute 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 name 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

Also Read..

Store the output of stored procedure into table/temp table.