Stored Procedure in SQL Server

Stored Procedure:

• 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 they 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.
     
Syntax:
 CREATE PROCEDURE   procedure_name [ ; number ]                                                            
[ { @parameter data_type } ]
AS sql_statement [...n]
   
Sample Code:

CREATE PROCEDURE sp_GetInventory
                @location varchar (10)
AS
SELECT
Product, Quantity
FROM
Inventory
WHERE
Warehouse = @location

Here, this stored procedure replaces the SELECT query,

SELECT
Product, Quantity
FROM
Inventory
WHERE
Warehouse = 'FL'

Where the query has to be compiled and executed every time and the warehouse manager should have knowledge about the sql queries and appropriate permissions to access the table information.

Where as in SP, the procedure is precompiled. Hence it can be executed easily by only specifying the location name.

EXECUTE sp_GetInventory 'FL'
EXECUTE sp_GetInventory 'NY'

Posted in: SQL Server Tutorial