Stored Procedure's :
A stored procedure is a group of Transact-SQL statements compiled into a single execution plan. Stored Procedures in SQL Server are similar to procedures in other programming languages in that they can:
- Accept input parameters and return multiple values in the form of output parameters to the calling procedure or batch.
- Contain programming statements that perform operations in the database, including calling other procedures.
- Return a status value to a calling procedure or batch to indicate success or failure (and the reason for failure).
- They allow modular programming.
- They allow faster execution.
- They can be reduce network traffic.
- They can be used as security mechanism.
Type of Stored Procedure's :
SQL Server supports five types of stored procedures. They are:System Stored Procedures (sp_)
Many administrative and informational activities SQL Server can be performed through system stored procedures. These system stored procedures are stored in the Master database and are identified by the sp_prefix. They can be executed from any database.Local Stored Procedures (sp_)
These procedures will be created in the user database. The user who create the will become the owner for that procedure.Temporary Stored Procedures (sp_)
Temporary stored procedures are stored in tempdb database. They can be used in the case where an application builds dynamic Transact-SQL statements that are executed several times. Instead of recompiling the T-SQL statements each time, a temporary stored procedure can be created and compiled on the first execution, then execute the precompiled plan multiple times. The temporary stored procedures can be local or global.Remote Stored Procedures (sp_)
They are legacy feature of SQL Server. Their functionality in T-SQL is limited to executing a stored procedure on a remote SQL Server installation. The distributed queries in SQL Server support this ability along with the ability to access tables on linked OLEDB data sources directly from local T-SQL statements.Extended Stored Procedures (sp_)
These are dynamic link libraries (DLLs) that SQL Server can dynamically load and execute. These procedures run directly in the address space of SQL Server and are programmed using SQL Server Open Data Services API. They are identified by the xp_prefix.
Creating a Stored Procedure :
The stored procedures can be created using the CREATE PROCEDURE statment.Syntax :
CREATE PROCEDURE procedure_name [( @parameter1 datatype [OUTPUT]), ( @parameter2 datatype..........) AS BEGIN SQL - Statements [RETURN] END
SQL SERVER Provide Two type of Parameters :
- Input Parameters
- Output Parameters
Input Parameters will be used to pass a value using the procedure call. Based on this value, the procedure execution will be done.
Output Parameters will be used to assign a value inside the procedure definition which can be used by the calling program. Before execution of the stored procedure, output parameter doesn't contain any value. Once the procedure execution completes, now the output parameter gets some value.
Example : Creating a Procedure to insert values into products table :
Create procedure pInsertProductst (@pid int, @pn char(10), @pqty int) AS BEGIN Insert into Products values (@pid, @pn, @pqty) END
Executing the Procedure :
We can execute a procedure will EXEC statement by specifying the procedure name and parameters list. If procedure call is only the statement to be executed, then no need to use exec statement. We can directly use procedure name and parameters list. Whereas, if we are calling the procedure from another procedure of T-SQL program, then we must use the EXEC statement to call the procedure.
Syntax :- ====== EXEC < Proc.Name > [Val1, Val2, Val1 output,...] Example :- ======= exec PInsertProducts (1, 'P1', 200)Ex: using output parameterrs
Write a program to calculating the following details for a given employee number.
- Basic Salary
- Benefits (Commission Percentage on salary)
- Net Salary
1. Creating a procedure to calculate Benefits
create procedure emp_benefits(@eno int, @add int output) as select @add = comm * sal * 0.01 from emp where empid = @eno
2. Creating a procedure to calculate Net Salary
create procedure emp_netsalary(@eid int) as BEGIN declare @bsal int, @add int, @nsal int exec emp_benefits @eid, @add output select @bsal = sal from emp where empid = @eid set @nsal = @bsal + @add select Empid = @eid, BasicSalary = @bsal, Benefits = @add, NetSalary = @nsal END
Error Handling in Stored Procedure :
In order to not get any runtime errors it is important to handle errors in stored procedure. Using a 'Begin Try' and 'Begin Catch' blocks error handling can be done in SQL Server.
create procedure < procedurename > (Parameter1,.....) as BEGIN Begin Try ------------- < Executable statements > End Try Begin Catch ---- Error Handling End Catch EndExample :
Create procedure PInsertProductst(@pid int, @pn char(10), @pqty int) AS BEGIN Begin Try Insert into products values(@pid, @pn, @pqty) End Try Begin Catch If Error_Number() = 2627 Print 'Duplicate product id value entered' Else Print Error_Message() End Catch End