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
