Like in dynamic function-CodePudding

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   %'''

CodePudding user response：

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