Stored Procedure in SQL Server
Stored procedure is a set of SQL statements with an assigned name stored in the database which can be used several times.
Syntax
CREATE PROCEDURE procedure_name
@parameter [type_schema_name] datatype
[ VARYING ] [ = default ] [ OUT | OUTPUT | READONLY ] ]
AS
BEGIN
[declaration_section]
executable_section
END;
Advantages of stored procedure
Stored procedures are used to:- Reduce network usage between clients and servers - stored procedures perform intermediate processing on the database server reducing unnecessary data transfer across the network
- Improve security - database administrator can control the users who access the stored procedure
- Improve the performance.
- Separate or abstract server side functions from the client side
- Access other database objects in a secure and uniform way
- Encapsulate business logic.
Following are the ways to create stored procedure.
1. Stored procedure with Input parameter
Example: Store procedure using input parameter.
Consider the following table titled 'Employee'.
| Id | Name | Gender | DepartmentId |
|---|
| 1 | Jay | Male | 1 |
| 2 | Ryan | Male | 2 |
| 3 | Anna | Female | 2 |
| 4 | Albert | Male | 1 |
| 5 | Satya | Female | 2 |
| 6 | Ravi | Male | 1 |
Create a Stored procedure with input parameter.
CREATE PROC spGetEmployeeByGenderAndDepartment
@Gender nvarchar(20),
@DepartmentId int
AS
BEGIN
SELECT Name, Gender, DepartmentId from Employee
WHERE Gender = @ Gender
End
EXECUTE spGetEmployeeByGenderAndDepartment @Gender = 'Male',
@DepartmentId = 1
| Name | Gender | DepartmentId |
|---|
| Jay | Male | 1 |
| Albert | Male | 1 |
| Ravi | Male | 1 |
2. Stored procedure with output parameter
Example: Consider the following table titled 'Employee'.
| Id | Name | Gender | DepartmentId |
|---|
| 1 | Jay | Male | 1 |
| 2 | Ryan | Male | 2 |
| 3 | Anna | Female | 2 |
| 4 | Albert | Male | 1 |
| 5 | Satya | Female | 2 |
| 6 | Ravi | Male | 1 |
Create a stored procedure by using output parameter.
CREATE PROC spGetEmployeeCountbygender
@Gender nvarchar(20),
@Employeecount int output
AS
BEGIN
SELECT @@Employeecount = COUNT(Id)
FROM Employee
WHERE Gender = @gender
END
Declare @TotalCount int
EXECUTE spGetEmployeeCountbygender @EmployeeCount = @TotalCount out, @Gender = 'Male'
PRINT @TotalCount
In the above example the variable @TotalCount int is declared.
When executing stored procedure, if the output keyword is not specified the result will be null.
Output:
3
