SQL Server - Stored Procedures

A procedure (also known as stored procedure) is a collection of SQL statements wrapped within the CREATE PROCEDURE / PROC statement. A procedure always contains a name, parameter lists, and SQL statements. It may also contain a conditional statement like IF or CASE or the Loops. A procedure can be invoked by using procedures, other procedures and applications.

Stored Procedure Features

• Stored Procedure increases the performance of the applications. Once stored procedures are created, they are compiled and stored in the database.
• Stored procedure reduces the traffic between application and database server. Because the application has to send only the stored procedure's name and parameters instead of sending multiple SQL statements.
• The stored procedure are reusable. A business logic can be implemented within a stored procedure which can be used by applications multiple times, or can be used by different modules of an application. This makes the database more consistent. If any change is required, it can be made in the stored procedure only.
• The stored procedures are more secure than the AdHoc queries. The permission can be granted to the user to execute the stored procedure without giving permission to the tables used in the stored procedure. The stored procedure helps to prevent the database from SQL Injection.

CREATE PROCEDURE

The CREATE PROCEDURE OR CREATE PROC statement is used to create a stored procedure. By default, a stored routine is associated with the default database. To associate the routine explicitly with a given database, specify the name as schema_name.procedure_name at the time of creation.

The stored procedure can also execute another stored procedure or a function that modularizes the code.

The syntax of using CREATE PROCEDURE statement in SQL Server (Transact-SQL) is given below:

CREATE { PROCEDURE | PROC } [schema_name.]procedure_name
  [ { @parameter [type_schema_name.] datatype }
    [ VARYING ] [ = default ] [ OUT | OUTPUT | READONLY ]
  ] [ ,...n ]

  [ WITH { ENCRYPTION | RECOMPILE | EXECUTE AS Clause } ]
  [ FOR REPLICATION ]

  AS

  BEGIN 
    --SQL Statements
  END;  

Parameters

To invoke a stored procedure, the EXEC or EXECUTE statement is used. See the syntax below:

EXEC procedure_name;

OR

EXECUTE procedure_name;

Create Procedure without Parameter

Consider a database table called Employee with the following records:

EmpID     Name      City         Age    Salary 
--------  --------  -----------  -----  --------
1         John      London       25     3000   
2         Marry     New York     24     2750   
3         Jo        Paris        27     2800   
4         Kim       Amsterdam    30     3100   
5         Ramesh    New Delhi    28     3000   
6         Huang     Beijing      28     2800   

To fetch all records of this table where Salary is greater than or equal to 3000 and count all the table rows, the following code can be used which creates a procedure named high_salary_employees:

CREATE PROCEDURE high_salary_employees
AS
BEGIN
  SELECT * FROM Employee WHERE Salary >= 3000;  
END;  
GO;

After successful execution, the procedure can be called as follows:

EXEC high_salary_employees;  

This will produce the result as shown below:

EmpID     Name      City         Age    Salary 
--------  --------  -----------  -----  --------
1         John      London       25     3000    
4         Kim       Amsterdam    30     3100   
5         Ramesh    New Delhi    28     3000    

(3 row(s) affected)

Create Procedure with One Parameter

In this procedure, a parameter named 'var1' of integer type is used which takes the value from user. Its body part fetches the records from the table using a SELECT statement and returns only those rows that is provided by user.

CREATE PROCEDURE get_employees @var1 INT
AS
BEGIN
  SELECT TOP @var1 * FROM Employee; 
END; 
GO; 

After successful execution, the procedure can be called as follows:

EXEC get_employees @var1 = 3;  

This will produce the result as shown below:

EmpID     Name     City        Age    Salary 
--------  -------  ----------  -----  --------
1         John     London      25     3000   
2         Marry    New York    24     2750   
3         Jo       Paris       27     2800    

(3 row(s) affected)

Create Procedure with Two Parameter

In this procedure, two parameter named 'salary' and 'age', of integer types are used. The procedures uses the values of these variables to fetch the records from the table.

CREATE PROCEDURE employees_salary_age @age INT, @salary INT
AS
BEGIN
  SELECT * FROM Employee
  WHERE Salary >= @salary AND Age <= @age; 
END; 
GO; 

After successful execution, the procedure can be called as follows:

EXEC employees_salary_age @salary =2800, @age = 28;  

This will produce the result as shown below:

EmpID     Name      City         Age    Salary 
--------  --------  -----------  -----  --------
1         John      London       25     3000     
3         Jo        Paris        27     2800    
5         Ramesh    New Delhi    28     3000   
6         Huang     Beijing      28     2800     

(4 row(s) affected)

Drop Procedure

Once a procedure is created in SQL Server (Transact-SQL), it can be removed by using DROP PROCEDURE statement.

Syntax

The syntax of removing a procedure in SQL Server (Transact-SQL) is given below:

DROP { PROC | PROCEDURE } [IF EXISTS] [schema_name.]procedure_name; 

The IF EXISTS is an optional parameter that conditionally removes procedure only if it exists on the database. If a procedure is deleted which does not exist, it will raise an error.

The schema_name is optional. If it is omitted, the procedure is dropped from the default database. The above statement removes a procedure named procedure_name.

Example:

To drop a procedure named CalcMaxSalary, the following statement can be used:

DROP PROCEDURE CalcMaxSalary;