Return Type Stored Procedure in SQL

We can get a return from a stored procedure from another stored procedure , here sp_GetEmpTypeId  is use to return EmpTypeId From tbl_EmpType for an EmpType and sp_CreateEmployee which is calling  sp_GetEmpTypeId will insert empname  and the returned typeId into the EmpDetails table for a EmpName
-----------------------------------------------
Create procedure sp_GetEmpTypeId
(
    @EmpType nvarchar(150),
    @EmpTypeId int=0 output
)
As
If  @EmpType is not Null
Begin
    If Not Exists(Select EmpType From tbl_EmpType where EmpType=@EmpType)
        Begin
            Insert Into tbl_EmpType Values(@EmpType)
            Select @EmpTypeId=@@IDENTITY
        End
    Else
        Begin
            Select @EmpTypeId=(Select EmpTypeId From tbl_EmpType where EmpType=@EmpType)
    End
End

----------------
create proc sp_CreateEmployee
(
    @empname nvarchar(50),
    @EmpType  nvarchar(50)
)
As
Declare @typeId int
Set @typeId=0
---This will return the @typeId for the @EmpType
Exec sp_GetEmpTypeId @EmpType, @typeId output
----
Insert into EmpDetails
        (
            empname,
            typeId
        )
        values
        (
            @empname,
            @typeId
        )
       
      

Related Posts: