I have a stored proc that inserts a record into a table and sets three output parameter values. There is a contition that then inserts a record into a second table. If this insert does not occur, my calling application gets the output values, but if the conditional insert does occur, the output values are empty. Can anyone tell me why? Here is the code:
CREATE Procedure usp_IMDetails_InsertTempDtl
@Orht_Id int,
@Pdt_id int,
@Ordt_UOM varchar(10),
@Ordt_UnitsOrdered decimal(9,2),
@Ordt_Cost decimal(9,2),
@Ordt_Price decimal(9,2),
@Ordt_DiscountPct decimal(9,2),
@Ordt_DiscountAmt decimal(9,2),
@Ordt_ExtendedAmt decimal(9,2),
@Ordt_AddDescText varchar(50),
@CreatedBy varchar(50),
@Ordt_Id int output,
@Pdt_ProductSKU varchar(25) output,
@Pdt_ProdDesc varchar(60) output
AS
BEGIN
BEGIN TRANSACTION
INSERT INTO ordt_OrderDtl (
Orht_Id,
Pdt_Id,
Ordt_UOM,
Ordt_UnitsOrdered,
Ordt_Cost,
Ordt_Price,
Ordt_DiscountPct,
Ordt_DiscountAmt,
Ordt_ExtendedAmt,
Ordt_CreatedBy,
Ordt_CreatedDate )
VALUES (
@Orht_Id,
@Pdt_Id,
@Ordt_UOM,
@Ordt_UnitsOrdered,
@Ordt_Cost,
@Ordt_Price,
@Ordt_DiscountPct,
@Ordt_DiscountAmt,
@Ordt_ExtendedAmt,
@CreatedBy,
GETDATE() )
IF @@ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
RETURN -1
END
Set @Ordt_Id = @@Identity
Select @Pdt_ProductSKU = pdt_productsku, @Pdt_ProdDesc = pdt_proddesc
From pdt_product
Where pdt_Id = @Pdt_Id
IF LEN(@Ordt_AddDescText) > 0
BEGIN
INSERT INTO nts_notes (
NTS_FileAssn,
NTS_ParTblId,
NTS_Type,
NTS_Text,
NTS_Destination,
NTS_CreatedBy,
NTS_CreatedDate)
VALUES (
'Ordt',
@Ordt_Id,
1,
@Ordt_AddDescText,
0,
@CreatedBy,
GETDATE())
IF @@ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
RETURN -2
END
END
COMMIT TRANSACTION
RETURN 1
END
GO