CODEWITHSUNDEEP
sql-servert-sql
benni_mac_b
benni_mac_b

Reputation: 8877

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

Upvotes: 2

Views: 100

Answers (3)

Steve Homer
Steve Homer

Reputation: 3922

It appears that your stored procedure can take an optional parameter @return_count. If this is the case then you can express this directly as

CREATE PROCEDURE myStoredProc
   @return_count int = 10

AS

BEGIN

.... my logic

END

Upvotes: 1

podiluska
podiluska

Reputation: 51494

You might want to consider

SELECT @return_count = ISNULL(@return_count, 10)

Upvotes: 7

Eric Hauenstein
Eric Hauenstein

Reputation: 2565

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.

Upvotes: 2

Related Questions