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 .
- IN: This is the Default Parameter for the procedure. It always receives the values from the users.
- OUT: This parameter always returns the values to the user .
- IN OUT: This parameter performs both the operations. It receives value from as well as sends the values to the user.
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 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
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
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 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.
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 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:
- First, initialize a variable of same data type as that of the output parameter. Here, we have declared a variable @count of type integer.
- 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.
- 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