A stored procedure is a group of Transact-SQL statements compiled into a single execution plan. SQL Stored procedures return data in four ways:
- Output parameters, which can return either data (such as an integer or character value) or a cursor variable (cursors are result sets that can be retrieved one row at a time).
- Return codes, which are always an integer value.
- A result set for each SELECT statement contained in the stored procedure or any other stored procedures called by the stored procedure.
- A global cursor that can be referenced outside the stored procedure
Why to use stored procedures in SQL server:
- Execution plan retention and reuse
- Query auto-parameterization
- Encapsulation of business rules and policies
- Application modularization
- Sharing of application logic between applications
- Access to database objects that is both secure and uniform
- Consistent, safe data modification
- Network bandwidth conservation
- Support for automatic execution at system start-up
- Enhanced hardware and software capabilities
- Improved security
- Reduced development cost and increased reliability
- Centralized security, administration, and maintenance for common routines
How to create Stored Procedure in SQL:
1. In Object Explorer, connect to an instance of SQL Server Database Engine and then expand that instance.
2. Expand Databases, expand the database in which the stored procedure belongs, and then expand Programmability.
3. Right-click Stored Procedures and then click New Stored Procedure.
4. On the Query menu, click Specify Values for Template Parameters.
5. In the Specify Values for Template Parameters dialog box, the Value column contains suggested values for the parameters. Accept the values or replace them with new values, and then click OK.
6. In the query editor, replace the SELECT statement with the statements for your procedure.
7. To test the syntax, on the Query menu, click Parse.
8. To create the stored procedure, on the Query menu, click Execute.
9. To save the script, on the File menu, click Save. Accept the file name or replace it with a new name, and then click Save.
Example:
CREATE PROCEDURE spVendorByState
@VendorState varchar(50)AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements. SET NOCOUNT ON;
-- Insert statements for procedure here SELECT VendorId,VendorFName,VendorLName,VendorCity,VendorState,VendorCountry,PostedDate,
VendorDescription FROM Vendor Where VendorState = @VendorState ORDER BY PostedDate
@VendorState varchar(50)AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements. SET NOCOUNT ON;
-- Insert statements for procedure here SELECT VendorId,VendorFName,VendorLName,VendorCity,VendorState,VendorCountry,PostedDate,
VendorDescription FROM Vendor Where VendorState = @VendorState ORDER BY PostedDate
There are some different concepts of stored procedures.
- A stored procedure is one or more SQL statements that have been compiled and stored with database. A stored procedure can be started by application code on the client.
- Stored procedure can improve database performance because the SQL statements in each procedure are only compiled and optimized the first time they are executed. In contrast SQL statements that are sent from a client to the server have to be compiled and optimized every time there are executed.
- In addition to SELECT statement, a stored procedure can contain other SQL statements such as INSERT, UPDATE, and DELETE. It also contains control-of-flow language.
- A trigger is a special type of procedure that executes when rows are inserted, updated or deleted from table.
- A user defined function (UDF) is a special type of procedure that can return a value or a table.
