By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,806 Members | 1,787 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,806 IT Pros & Developers. It's quick & easy.

Maintaining variable values

P: 2
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_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 9 '06 #1
Share this question for a faster answer!
Share on Google+

Post your reply

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