Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

NULL parameter check - WHERE vs IF

Currently reviewing a stored procedure to which @return_count is a parameter. Below is a standalone example of the two ways that I can set its value if a NULL is passed in.

Cant figure out if the query optimizer would treat them the same or if one method is better than the other.

DECLARE @return_count int
SET @return_count = null
IF @return_count is null
  BEGIN
    SELECT @return_count = 10
  END
GO  

DECLARE @return_count int
SET @return_count = null    
BEGIN
  SELECT @return_count = 10
  WHERE @return_count is null
END
like image 974
benni_mac_b Avatar asked Dec 05 '25 03:12

benni_mac_b


2 Answers

You might want to consider

SELECT @return_count = ISNULL(@return_count, 10)
like image 174
podiluska Avatar answered Dec 07 '25 03:12

podiluska


They return different exec plans, and the former is considerably less complex. The latter involves filter and scan operations. Since it doesn't appear that this needs to scale, I'd go with the former.

like image 23
Eric Hauenstein Avatar answered Dec 07 '25 04:12

Eric Hauenstein