Stored Procedure in SQL server 2005

What is Stored Procedure?

Stored procedures are an important aspect in all database programs. A stored procedure is a set of one or more SQL statements that are stored together in database. Microsoft SQL Server provides the Stored Procedure mechanism to simplify the database development process by grouping Transact-SQL statements into manageable blocks. 

Benefits of Stored Procedures

• Precompiled execution. SQL Server compiles each stored procedure once and then reutilizes the execution plan. This results in tremendous performance boosts when stored procedures are called repeatedly.
• Reduced client/server traffic. If network bandwidth is a concern in your environment, you'll be happy to learn that stored procedures can reduce long SQL queries to a single line that is transmitted over the wire.
• Efficient reuse of code and programming abstraction. Stored procedures can be used by multiple users and client programs. If you utilize them in a planned manner, you'll find the development cycle takes less time.
• Enhanced security controls. You can grant users permission to execute a stored procedure independently of underlying table permissions.

Creating Stored Procedures

 Syntax:

CREATE PROCEDURE  p_GetOrderByName  -- Name of stored procedure
@Name varchar(10)                                             -- input parameters of the SP
AS
SELECT Item, Quantity                  -- Query or other task you want to do
FROM Order                                   -- in stored procedure
WHERE Name = @Name 

 Executing/Calling Stored Procedures

exec p_GetOrderByName 'bharat' -- 'bharat' is the input parameter required by

                                                         --procedure

 

Order Table

OrderID						Item	Name		Quantity
142						Green beans	bharat		100
825						Corn	Ravi		140
512						Lima beans	Manoj		180
491						Tomatoes	Naseem		80
379						Watermelon	Vipin		85

result:

Item                       Quantity

Green beans           100