Like in dynamic function

Question

The code below works well. I however have issues trying to turn it into a like statement that I need some assistance with

CREATE PROCEDURE [dbo].[searcher]
    @deliverer nvarchar (100)
AS
BEGIN
    DECLARE @sql nvarchar(1000)

    SET @sql = 'SELECT location, deliverer, charger FROM Store where 1=1'

    IF (@deliverer IS NOT NULL)
        SET @sql = @sql + ' and deliverer =@pt'

    DECLARE @t1 as TABLE 
                   (
                       location varchar(1000), 
                       deliverer varchar(100), 
                       charger varchar(100)
                   )

    INSERT INTO t1
        EXEC sp_executesql @sql,
                N'@pt nvarchar(100)',
                @pt=location

    SELECT * FROM t1
END

So far, I have tried the code below but with not much success

DECLARE @pt nvarchar (100)

SET @pt = '%' + @pt + '%'

IF (@deliverer IS NOT NULL)
    SET @sql = @sql + ' and deliverer like @pt'

I have also tried;

DECLARE @pt nvarchar (100)

IF (@deliverer IS NOT NULL)
    SET @sql = @sql + ' and deliverer like ''% + @pt + %'''

If your stored procedure parameter is `@deliverer` and your dynamic SQL parameter is `@pt`, I believe your sp_executesql execution should assign the parameter as `@pt = @deliverer`. As for adding wildcards, you can either add them before the call with `SET @deliverer = '%' + @deliverer + '%'` or add them in the dynamic SQL with `SET @sql =@sql + ' and deliverer like ''%'' + @pt + ''%'''`. Note the doubled up quotes around the `%`. The variable `@pt` is not quoted — T N, Mar 18 '22 at 04:01
Aside... you need to be consistent with your `varchar` and `nvarchar` data type usage, otherwise at some point you'll get clobbered characters - either in matching or in presentation. — AlwaysLearning, Mar 18 '22 at 04:11
@TN, that works a charm. Happy to accept you response if you post it as an answer. — Kamikaze K, Mar 18 '22 at 04:16
@TN, what if I wanted to make the where clause check multiple values in a the deliverer column. say; `WHERE deliverer LIKE ('%ichael%') OR deliverer LIKE ('%ohn%') OR deliverer LIKE ('%cristine%') ` how will the `SET @sql =` and the `exec` be formatted? — Kamikaze K, Mar 18 '22 at 11:23
@KamikazeK - How will you be passing in multiple values to your stored procedure? Will it be a comma separated list? If so, you will likely need to update your dynamic sql with something like `AND EXISTS(SELECT * FROM STRING_SPLIT(@pt, '','') WHERE deliverer LIKE ''%'' + value + ''%'')'`. (Note the doubled-up quotes around the comma and wildcard.) I believe the STRING_SPLIT() function requires SQL Server 2017 or later. — T N, Mar 18 '22 at 14:26

score 2 · Accepted Answer · answered Mar 18 '22 at 04:24

If your stored procedure parameter is @deliverer and your dynamic SQL parameter is @pt, I believe your sp_executesql execution should assign the parameter as @pt = @deliverer.

As for adding wildcards, you can either add them before the call with

SET @deliverer = '%' + @deliverer + '%'

or add them in the dynamic SQL with

SET @sql = @sql + ' and deliverer like ''%'' + @pt + ''%'''

Note the doubled up quotes around the %. The variable @pt is not quoted

Like in dynamic function

1 Answers1