TSQL using a wildcard in a where clause with dynamic sql

Question

It appears that using the LIKE in a condition with wildcards and a variable inside of dynamic sql doesn't work, although it doesn't give an error. Here's an example.

The column called code has values like A0B01C02,A0B02C2D05,A0B02C2D05, etc and I am trying to match on rows containing a subset like 'B1'. When I do this it works and returns results as expected.

set @sql='select * from table where code like ''%B01%'''
exec sp_executesql @sql

If I hardcode the value of the variable set @code='B01' and modify the sql statement to concatenate the quotes and wildcards:

set @sql='select * from table where code like ' +''''+ '%'+@code + '%' + ''''
exec sp_executesql @sql

This returns the results as expected, but I had to hard code the variable. However, when I need to make the match using a variable for B01 and that the variable is set with a select statement, I don't get any results returned. I define an nvarchar like this:

set @code=(select top 1 code from anotherTable where USERID=@PersonId)

I confirmed that the select statement above returns the expected code, however. There is no error, but the query is "executed successfully". Am I missing something in the syntax for the where clause?

Answer 1

You can find a discussion of this at http://ask.sqlservercentral.com/questions/275/dynamic-where-clause-how-can-i-use-a-variable-in-an-in-predicate/312#312
My answer was to do the Parse By Comma Function.

/*****************************************************************
**** Parse A Comma Delimited String Into A Table
*****************************************************************/
ALTER  FUNCTION [dbo].[ParseByComma] (
    @String VARCHAR(600) )
RETURNS @TblSubString TABLE
(
    VarSubString VARCHAR(50)
)
AS
BEGIN
    DECLARE @intPos INT,
            @SubStr VARCHAR(50)

    -- Remove All Spaces
    SET @String = REPLACE(@String, ' ','')
    -- Find The First Comma
    SET @IntPos = CHARINDEX(',', @String)
    -- Loop Until There Is Nothing Left Of @String
    WHILE @IntPos > 0
    BEGIN
        -- Extract The String
        SET @SubStr = SUBSTRING(@String, 0, @IntPos)
        -- Insert The String Into The Table
        INSERT INTO @TblSubString (VarSubString) VALUES (@SubStr)
        -- Remove The String & Comma Separator From The Original
        SET @String = SUBSTRING(@String, LEN(@SubStr) + 2, LEN(@String) - LEN(@SubStr) + 1)
        -- Get The New Index To The String
        SET @IntPos = CHARINDEX(',', @String)
    END
    -- Return The Last One
    INSERT INTO @TblSubString (VarSubString) VALUES (@String)
RETURN
END

Answer 2

Hi Dave,

I do not have a SQL Server in front of me at the moment but I wonder if this syntax might work for you?

set @sql='SELECT * FROM table WHERE UPPER(code) LIKE ''%''||(UPPER(COALESCE('''||@code||''',code)))||''%'' ' exec sp_executesql @sql

Best regards,

Kevin