Stored procedure executing, but not getting expected results

Transact-SQL
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);

1 Like
5

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

1 Like