I have written a stored proc to write order details to the db. If there is additional description text, I am writting it out to another note table. i initially performed this using EXEC with another stored proc. When doing this, upon returning to my VB.Net app, the @Ordt_Id, @PDT_ProductSKU , and the @Pdt_ProdDesc variables no longer contain any data. I moved the note creation code into this stored proc, but I get the same results. If there is no note record created then the values are fine. How can i fix this?
CREATE Procedure usp_IMDetails_I nsertTempDtl
@Orht_Id int,
@Pdt_id int,
@Ordt_UOM varchar(10),
@Ordt_UnitsOrde red decimal(9,2),
@Ordt_Cost decimal(9,2),
@Ordt_Price decimal(9,2),
@Ordt_DiscountP ct decimal(9,2),
@Ordt_DiscountA mt decimal(9,2),
@Ordt_ExtendedA mt decimal(9,2),
@Ordt_AddDescTe xt 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_UnitsOrder ed,
Ordt_Cost,
Ordt_Price,
Ordt_DiscountPc t,
Ordt_DiscountAm t,
Ordt_ExtendedAm t,
Ordt_CreatedBy,
Ordt_CreatedDat e )
VALUES (
@Orht_Id,
@Pdt_Id,
@Ordt_UOM,
@Ordt_UnitsOrde red,
@Ordt_Cost,
@Ordt_Price,
@Ordt_DiscountP ct,
@Ordt_DiscountA mt,
@Ordt_ExtendedA mt,
@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_AddDe scText) > 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_AddDescTe xt,
0,
@CreatedBy,
GETDATE())
IF @@ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
RETURN -2
END
END
COMMIT TRANSACTION
RETURN 1
END
GO