Compare t-sql 2012 column to parameter value

Transact-SQL
1

In an existing t-sql 2012 stored procedure, I want to add a new parameter called @newparam defined probably as int.

This parameter valuet will be compared against the production column called 'sExclude' is defined as (bit,null). I checked all the

values for 'sExclude' in production and there are no records where the production value is null.

What I want to do is select is the following:

  1. Select sExclude when @newparm value matches the value in the sExclude column.
    In other words when sExclude = 0 and @newparm = 0
    or when sExclude = 1 and @newparm = 1.
  2. When the @newparm value is any other value like 2, I do not care cared of the sExclude value is. I just want to select all

records regardless of what the sExclude value is actually in production.

I am trying to determine how to write the t-sql for this logic in a where clause.

Would I say

'Where sExclude = @newparm or (@newparm not in (0,1))

Would you tell me how you would setup the t-sql 2012 for what i need in this situation?

2

I normally do this with a @parameter value of NULL being the "wildcard"

WHERE (@MyParam1 IS NULL OR MyColumn1 = @MyParam1)
      AND (@MyParam2 IS NULL OR MyColumn2 = @MyParam2)

its easy to write, but not very efficient of course, so where performance matters (and there are several such parameters) I would use dynamic SQL. Something like this:

SELECT @SQL = CASE WHEN @MyParam1 IS NULL THEN '' ELSE ' AND MyCol1 = @MyParam1' END
            + CASE WHEN @MyParam2 IS NULL THEN '' ELSE ' AND MyCol2 = @MyParam2' END
            ...

i.e. only the "in use" @Parameters are included in the dynamic @SQL

Then something like:

SELECT @SQL = 'SELECT Col1, Col2, ...
FROM MyTable
WHERE 1=1'
+ @SQL 
+ ' ORDER BY SortCol1,  SortCol2, ...'

EXEC sp_executesql @SQL
     , N'@MyParam1 int, @MyParam2 varchar(1234), ...'
     , @MyParam1 = @MyParam1
     , @MyParam2 = @MyParam2
     , ...

Note that it does not matter if any definitions and @Parameters are included in the EXEC of sp_executesql but are not actually included in the @SQL.

Significant benefit of using sp_exectesql in this way is that the query plan is cached and reused (assuming the same @SQL is generated, albeit with different values for the @Parameters)

1 Like