T-SQL - 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 T-SQL (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
schema_name |
Specify the name of the schema that owns the stored procedure. |
procedure_name |
Specify the name to assign to this procedure. |
@parameter |
Specify one or more parameters passed into the procedure. |
type_schema_name |
Specify the data type of the parameter and the schema to which the data type belongs. |
datatype |
Specify the data type for @parameter. |
VARYING |
Specify for cursor parameters when the result set is an output parameter. |
default |
Specify the default value to assign to @parameter. |
OUT |
Indicates that @parameter is an output parameter. |
OUTPUT |
Indicates that @parameter is an output parameter. |
READONLY |
Indicates that @parameter can not be overwritten by the stored procedure. |
ENCRYPTION |
Indicates that the source for the stored procedure will not be stored as plain text in the system views in SQL Server. |
RECOMPILE |
Indicates that a query plan will not be cached for this stored procedure. |
EXECUTE AS clause |
Sets the security context to execute the stored procedure. |
FOR REPLICATION |
Indicates that the stored procedure is executed only during replication. |
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 T-SQL (Transact-SQL), it can be removed by using DROP PROCEDURE statement.
Syntax
The syntax of removing a procedure in T-SQL (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;