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!
