SQLTeam.com | Weblogs | Forums

Sql statement in sp is ignored


#1

I have an sp that has the following statements

INSERT INTO stockorderdetails(productid,optionid1value,optionid2value,qtyrec)
		VALUES(@productid,@skucolor,@skumodel,@numadded)
		
    SELECT @stockorderdetailsid=@@IDENTITY
    
    SELECT	@attributeStockId = dbo.ufn_GetAttributeStockIdFromID (@productid,@skucolor,@skumodel)
    
	UPDATE attributestock 
		SET numberinstock	= numberinstock + @numadded 
		WHERE	id = @attributeStockId
		
	INSERT INTO [dbo].[stockOrderDetailsChanges] (stockOrderDetailsId, qtyrec, prevCost, newCost, userid, attributeStockId)
       	VALUES(@stockorderdetailsid,@numadded,@cost,@cost,@userid,@attributeStockId)
       	
       	
   insert into updatestocklogbyattribute(attributeid,qty)
  values(@attributeStockId,@numadded)

Now the strange thing is that I am not getting any error messages and but the middle statmenet is not running (and it's running the statement after it)
this is the statement that sometimes is not running

INSERT INTO [dbo].[stockOrderDetailsChanges] (stockOrderDetailsId, qtyrec, prevCost, newCost, userid, attributeStockId)
VALUES(@stockorderdetailsid,@numadded,@cost,@cost,@userid,@attributeStockId)

the one before it and after is

How can I debug the issue if there are no errors?


#2

What is the indication that the statement is not running? Are you looking at the number of rows in the table before and after? Does the table have a trigger on it? It is possible that the trigger is reverting the insert based on values inserted. Otherwise, either you will get an error message, or a row should be inserted into the table.


#3

the record is not submitted - the record is in the next table that I add to
there are no triggers and no error messages
it's very strange


#4

You are overlooking something - but I don't know what it is; not sufficient information in the code fragment.

Try the following and see what it shows

-- insert these before your insert statement that does not execute
select count(*) from [dbo].[stockOrderDetailsChanges];

-- this is your insert statement that does not execute 
INSERT INTO [dbo].[stockOrderDetailsChanges] (stockOrderDetailsId, qtyrec, prevCost, newCost, userid, attributeStockId)
       	VALUES(@stockorderdetailsid,@numadded,@cost,@cost,@userid,@attributeStockId);

-- insert these statements after your insert statement that does not execute
select @@rowcount;
select count(*) from [dbo].[stockOrderDetailsChanges];

-- rest of your query here

#5

Some unique or check constraint is likely being violated. There might be a CATCH block in effect that is "erasing" the error message or it's not getting properly passed back up to the caller.


#6

My guess is that something is happening around setting @stockorderdetailsid. Run your code and print out the result from the funciton for @stockorderdetailsid. You may not be getting the value you're expecting or any value at all. There could be a chance you're picking up a bad value ..or.. nothing at all. This would cause the update to fail if there is no record in AttributeStock table for the @attributeStockId.