SQLTeam.com | Weblogs | Forums

Stored procedure executing, but not getting expected results


#1

I have a stored procedure that is executed via Javascript then ColdFusion. It seems that sometimes the records are not inserted. I am wondering if it is the DELETE statement before the INSERT is somehow not running serialized. Do I need to put some kind of transaction or lock to be sure it does? I'm not sure what else would cuase the problem I am seeing.

ALTER PROCEDURE [dbo].[click_handler_on] (
	@user_id INT = NULL,
	@link_id INT = NULL
	)
AS
BEGIN

    DELETE FROM		tbl_leaning
    WHERE			user_id = @user_id
    AND				link_id = @link_id
    AND				leaning_code = 3	
    OR				leaning_code = 4;

    INSERT INTO		tbl_leaning (link_id, user_id, leaning_code)
    VALUES			(@link_id, @user_id, 4);
END

#2

A wild guess - try changing:

    AND				leaning_code = 3	
    OR				leaning_code = 4
;

to

    AND				leaning_code in (3,4)

#3

That seems to have fixed it. Thank you very much.


#4

Another way is to use parenthesis which is a good habit to get in when using OR

DELETE FROM tbl_leaning
WHERE user_id = @user_id
AND link_id = @link_id
AND (leaning_code = 3
OR leaning_code = 4);


#5

@djj55 good point, as long as it's not over done. To many unneeded parenthesis will cludder it up.