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
)
Good content, helpful to developers
ReplyDeleteUdal Kr.Bharti
www.ksoftware.co.in