.gif)
Using Stored procedure with parameters in Sql Server
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
It will fetch all records from table but some time we required to fetch only specific rows which meet a specific condition at that we have to use Stored procedure with parameters .
In the following stored procedure we supply name and age of employee and the procedure will return the rows which matches that supply name and age .
USE ConvergenceTest;
GO
IF OBJECT_ID ( 'GetEmpDetail', 'P' ) IS NOT NULL
DROP PROCEDURE GetEmpDetail;
GO
CREATE PROCEDURE GetEmpDetail
@Name nvarchar(50),
@Age int
AS
SELECT Name, Age, Department
FROM empdetail
WHERE Name = @Name AND Age = @Age;
GO
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.
