sql variables

Question

Can somebody help me figure out why the sql statement doesn't like the following line ' and ' + @SearchCat + 'like '%'+@Keywords+'%''. It has to do with the number of single quotes but I can't figure out. How do the quotes work. What's the logic?

DECLARE @strStatement varchar(550) 
declare @state as varchar(50)
declare @district as varchar(50)
declare @courttype as varchar(50)
declare @SearchCat as varchar(50)
declare @KeyWords as varchar (50)

select @State ='FL'
select @district = '11'
select @courtType = '1'
select @SearchCat='CaseNumber'
select @KeyWords='File'


select @strStatement= 'SELECT CaseNumber FROM app_Case 
      where State ='''+ @State+
      ''' and District='''+ @District+
      ' and ' + @SearchCat + 'like '%'+@Keywords+'%''


exec (@strStatement)

Answer 1

I figure it out. I was missing a space before 'like'

Answer 2

I was missing a space before 'like'

You've also got the wrong number of single-quotes around your ‘%’ characters, which will confuse it.

Incidentally, you've made yourself a nice little SQL injection security hole there, from inside SQL itself! If one of the parameters contains an apostrophe your sqlStatement will break and any rogue SQL in the parameter name would be executed.

You can use the REPLACE function to double up single quotes to prevent this attack:

' AND '+QUOTENAME(@SearchCat)+' LIKE ''%'+REPLACE(@Keywords, '''', '''''')+'%''...'

(The QUOTENAME is needed if the column name contains out-of-band characters or is a reserved word.)

A cleaner (but quite verbose) approach to generating the SQL than tediously REPLACEing every string literal yourself is to use sp_executesql. For example:

SELECT @strStatement= N'
    SELECT @Number= CaseNumber FROM app_Case 
    WHERE State=@State AND District=@District
    AND '+QUOTENAME(@SearchCat)+N' LIKE ''%''+@Keywords+''%''
';
SELECT @params= N'@State varchar(50), @District varchar(50), @Keywords varchar(50), @Number int OUTPUT';

EXECUTE sp_executesql @strStatement, @params, @State, @District, @Keywords, @Number OUTPUT;

Incidentally if @searchCat can only have a small number of different values, you can use a workaround to avoid having to do any of this laborious dynamic-SQL nonsense at all:

SELECT CaseNumber FROM app_Case
WHERE State=@State AND District=@District
AND CASE @searchCat
    WHEN 'searchableColumnA' THEN searchableColumnA
    WHEN 'searchableColumnB' THEN searchableColumnB
END LIKE '%'+@Keywords+'%';

See this rather good exploration of dynamically-created SQL statements in T-SQL for more background and some of the risks you face.