Stored procedure problem please help
-
Hello
I have a stored procedure that returns a value correctly the first time through a application an hour later the stored procedure returns the value again with wrong data.
First Time = Returns a parent with child results (correct)
Second time - Returns the same parent with different child results (incorrect)
When executing it through the database all result come back correctly.
This has happen three times yesterday.
stored proc
CREATE procedure prProcessFSM500
(
@LineId smallint --14 for FSM500
,@ClientId int
,@Cards varchar(1000)
,@ProductId int --passed in by app as user selects product
,@OrderId int output --set on first call when = 0, then app returns same value
,@BrickId varchar(20) output --MES generated
,@ClientOrderNr varchar(30) output
,@Denom varchar(25) output
,@TestMode bit = 0
,@Error uderror output
)
as
begin
declare @CardsTable table (
ItemId varchar(20)
 
declare @CardsCount tinyint
,@POID int
,@ProductTypeId char(1)
,@PackingTypeId smallint
,@MachineId smallint
,@Weight real
,@BrickIdVerify varchar(20)
,@BrickVerifyCount int
set nocount on
insert @CardsTable (ItemId)
select ListItem
from fnListToRows(@Cards, ',')
--select * from @CardsTable --???
select top 1 @Error = ''
,@POID = I.POID
,@OrderId = case @OrderId when 0 then PO.OrderId else @OrderId end --first call from app will not have the orderid
,@ClientOrderNr = O.ClientOrderNr
from @CardsTable C
join tbItem I (nolock)
on C.ItemId = I.ItemId
and i.clientid = @ClientId
and i.productid = @ProductId
join tbProductionOrder PO (nolock)
on I.POID = PO.POID
join tbOrder O (nolock)
on PO.OrderId = O.OrderId
--select @Error Error, @POID POID, @OrderId OrderId, @clientOrderNr CON --???
select @CardsCount = count(*)
from @CardsTable C
join tbItem I (nolock)
on c.itemid = i.itemid
and i.clientid = @ClientId
and i.productid = @ProductId
and i.parentitemid is null
and i.ItemStatusId in (1,5,6) -- '1=GOOD,5=REMOVED,6=FINISHED'
join tbProductionOrder PO (nolock)
on I.POID = PO.POID
and PO.OrderId = @OrderId
--select @CardsCount CardsCount --???
if @CardsCount <> 10
select @Error = 'Only ' + convert(varchar,@CardsCount) + ' unpacked cards found for this Client, Order, Product with status GOOD, REMOVED or FINISHED'
else
begin
select @ProductTypeId = ProductTypeId
,@MachineId = MachineId
From tbMachineLine (nolock)
Where LineId = @LineId
If @@Rowcount <> 1
Select @Error = 'No Product Type for Line '+ convert(varchar, @LineId)
else
begin
-- Get the PackingTypeId for the Product and ProductType
select @Weight = P.Weight
,@Denom = rtrim(DenomPrefix) + Denom
,@PackingTypeId = PT.PackingTypeId
From tbProduct P (nolock)
join tbPackingType PT (nolock)
on P.ProductId = PT.ProductId
and P.ProductTypeId = PT.ProductTypeId
Where P.ProductId = @ProductId
and P.ProductTypeId = @ProductTypeId
If @@Rowcount <> 1
Select @Error = 'Error retrieving Weight or Packing Type for Product '+ convert(varchar,@ProductId)+' and Type '+convert(varchar, @ProductTypeId)
else if @TestMode = 1
select @BrickId = 'ABCD' + replace(convert(varchar, getdate(), 14),':','')
else
begin
exec prUpdBrickParentTbItem_FSM500
@Error output
,@Poid
,@ClientId
,@MachineID
,@ProductId
,@ProductTypeId
,null --Old tracking number
,@Cards
,0 -- @InitialWeight
,@Weight -- @Weight
,@PackingTypeId
,null -- SGSNo
,@BrickId output
if isnull(@Error, '') = ''
if isnull(@BrickId, '') = ''
select @Error = 'Error inserting / updating brick and cards'
else if (
select count(*)
from @CardsTable C
join tbItem I (nolock)
on C.ItemId = I.ItemId
where I.parentitemid = @BrickId
and I.clientid = @ClientId
 
<> 10select @Error = 'Error inserting / updating brick and cards'
end
end
end
end
GO
Not sure if this has being cached if so the how do i remove the result set from cache. I only know of removing the stored proc from cache DBCC FREEPROCCACHE Is there any way of avoiding this from happening
The stored procedure is executed from the database every 5 sec.
Please advise
Thanks
Wayne
-
I won't pretend that I understand what the proc is doing, but I would look at two things:
either the app is sending the wrong parameters?
or if you have connection pooling set to ON, this might be messing it up?
Just speculating, though...
/Kenneth
-
I'd dig into the data. If it's returning things you don't expect, I usually look for data problems.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com -
Digging into the data is always a good idea, though the poster explained it as the 'error' occured when running from the app, but when validating the same thing 'directly' it worked as expected - thus the data should be ok...
At least that's how I understood the problem...
/Kenneth
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply