Stored Procedure

A stored procedure is a set of one or more SQL statements that are stored together in database. To create a stored procedure use CREATE PROCEDURE statement. To use the stored procedure you send a request for it to be executed. When server recieves the request, it executes the stored procedure.

Stored procedures assist in achieving a consistent implementation of logic across applications. The SQL statements and logic needed to perform a commonly performed task can be designed, coded, and tested once in a stored procedure. Each application needing to perform that task can then simply execute the stored procedure. Coding business logic into a single stored procedure also offers a single point of control for ensuring that business rules are correctly enforced.

Stored procedures can also improve performance. Many tasks are implemented as a series of SQL statements. Conditional logic applied to the results of the first SQL statements determines which subsequent SQL statements are executed. If these SQL statements and conditional logic are written into a stored procedure, they become part of a single execution plan on the server. The results do not have to be returned to the client to have the conditional logic applied; all of the work is done on the server.

There are some 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 sontrast SQL statements that are sent from a client to the server have to be compiled and optimized everytime ther are executed.
  
  
• In addition to SELECT statement, a stored procedure can contain othe SQL statements such as INSERT,UPDATE,DELETE. It also contain 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.

Example:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE spEmpByState
          @EmpState 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 EmpId,
                       EmpFName,
                       EmpLName,
                       EmpCity,
                       EmpState,
                       EmpCountry,
                       PostedDate,
                       EmpDescription
          FROM EmpTable Where EmpState = @EmpState ORDER BY PostedDate

END
GO

You can execute your stored procedure like this:

EXEC  spEmpByState
EXECUTE  spEmpByState