Search code examples
t-sqlselectstored-proceduressql-like

Is it possible to use a parameter for LIKE / NOT LIKE

I am attempting to pass parameters for LIKE / NOT LIKE and the % signs to make a dynamic query that runs based on what is passed from the application. I get an error though as TSQL is confused about the parameter for like/not like as its not Boolean. Is it possible to do it this way or am I going to have to write multiple if statements to check to see what parameters are being passed.

@fullName varchar(100), @SrchCriteria1 varchar(15), @SrchCriteria2 varchar(15), 
@fullName1 varchar(100), @SrchCriteria3 varchar(15), @SrchCriteria4 varchar(15),
@fullName2 varchar(100), @SrchCriteria5 varchar(15), @SrchCriteria6 varchar(15),
@qualifer varchar(10), @qualifer1 varchar(10), @qualifer2 varchar(10)

SELECT d.*
FROM   defendants_ALL d, #tmpFullname t1, #tmpFullname1 t2, #tmpFullname2 t3
WHERE  d.combined_name + @qualifer + @SrchCriteria1 + t1.Item + @SrchCriteria2
and  d.combined_name + @qualifer + @SrchCriteria3 + t2.Item + @SrchCriteria4
and  d.combined_name + @qualifer + @SrchCriteria5 + t3.Item + @SrchCriteria6

EXEC uspJudgments @qualifier = 'LIKE', @qualifier1 = 'LIKE', @qualifier = 'NOT LIKE', @fullName = 'johns', @fullName1 = 'jim,j.', @SrchCriteria1 = '%', @SrchCriteria2 = '%',  @SrchCriteria3 = '%', @SrchCriteria4 = '%', @fullName2 = 'johnson', @SrchCriteria5 = '%', @SrchCriteria6 = '%'

So that should return all combinations of jim johns and j. johns but will not include jim johnson and j. johnson. I know that is a rare combination but I couldn't think of a better one ATM that is more common.

Dynamic SQL:

DECLARE @Query NVarChar(1024)
SET @Query = 'SELECT d.* FROM defendants_ALL d, #tmpFullname t1, #tmpFullname1 t2, #tmpFullname2 t3'  +
'WHERE d.combined_name' + @qualifier + @SrchCriteria1 + 't1.Item' + @SrchCriteria2 +
' and d.combined_name' + @qualifier + @SrchCriteria3 + 't2.Item' + @SrchCriteria4 +
' and d.combined_name' + @qualifier + @SrchCriteria5 + 't3.Item' + @SrchCriteria6
Solution

  • LIKE accepts an expression for the pattern, e.g. d.combined_name LIKE @SrchCriteria.

    You are attempting to assemble a dynamic query. To do so, you need to create the entire query in a string and then use EXECUTE:

    declare @Query as NVarChar(1024) = 'SELECT d.* FROM ...' +
  'WHERE d.combined_name ' + @qualifer + @SrchCriteria1 + t1.Item + @SrchCriteria2 +
  '...'

    The statement may be assembled in several steps before being passed to EXECUTE.