Stored procedures in SQL Server are same as the procedures used in other programming languages .The stored procedure can Accept input parameters and return multiple values as output parameters to the calling procedure.They contain statements that perform operations in the database.They can also call other procedures.
The basic things about stored procedure are:
Stored procedure is the combination of one or more SQL statements.
Stored procedure's have been compiled and stored in database. We can call stored procedure by application code on the client.
Stored Procedures improve the performance of database because they are compiled only the first time they are executed.
Types of Stored Procedures
User-defined Stored Procedures :-These are written by user using T-Sql statement.
Extended Stored Procedures:-Extended stored procedures let you create your own external routines in a programming language such as C.
System Stored Procedures:-All administrative activities in SQL Server 2005 are performed through a special kind of procedure known as a system stored procedure. For example, sys.sp_changedbowner is a system stored procedure.
How To Create a Strored Procedure
We use CREATE PROCEDURE command to create a stored procedure.Here i am giving a example of simple stored procedure that will retrive all emplyoee detail from table .
USE ConvergenceTest;
GO
IF OBJECT_ID ( 'GetEmpDetail', 'P' ) IS NOT NULL
DROP PROCEDURE GetEmpDetail;
GO
CREATE PROCEDURE GetEmpDetail
AS
SELECT Name, age, Department,address
FROM empdetail;
GO
Video Tutorial Showing how to create sql server 2005 Stored Procedure.
