Create Stored Procedure with output parameter and how to call stored procedure with output.

 Create proc with input parameter:

CREATE PROCEDURE Usp_GetEmployee

(

   @EmpId int

)

as

begin

select * from Emp where EmpId=@EmpId

end

 Create proc with Output parameter:

CREATE PROC Usp_OutPutEmp

(

  @EmpId int ,

  @empName varchar output

)

as 

begin

SELECT @empName= EmpName  FROM Emp WHERE EmpId=@EmpId

end

---------------------Call Proc with output Parameter------------

DECLARE @EmpName varchar(30)

exec Usp_OutPutEmp 1,@EmpName output

print @EmpName