Friday, August 06, 2010

Execute Stored Procedure with Output Parameters?

SQL Server stored procedure that you can call is one that returns one or more OUT parameters, which are parameters that the stored procedure uses to return data back to the calling application. Here is the sample procedure which i am using with output parameters.

CREATE PROCEDURE GetEmployeeRoles
   @employeeID INT,
   @Roles VARCHAR(200) OUTPUT
AS
BEGIN
   SELECT @Roles = Roles 
   FROM HumanResources.Employee 
   WHERE EmployeeID = @employeeID
END

For testing this using SQL Query Analyzer or Editor you can use below code  to see the results.

DECLARE @roles VARCHAR(200)
EXECUTE GetEmployeeRoles 6,@roles OUTPUT
SELECT @roles as EmployeeRoles

Hope this helps!

No comments:

Post a Comment