471,075 Members | 809 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,075 software developers and data experts.

output parameters

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
Jun 10 '06 #1
0 8689

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by Bari Allen | last post: by
5 posts views Thread by vivienne.netherwood | last post: by
4 posts views Thread by Janaka | last post: by
2 posts views Thread by Bari Allen | last post: by
3 posts views Thread by juststarter | last post: by
1 post views Thread by Joe Van Meer | last post: by
1 post views Thread by Garth Wells | last post: by
reply views Thread by leo001 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.