How do I dynamically build a like clause in an executable sql stored procedure that uses EXEC sp_executesql?

Question

The following stored procedure works correctly execpt when I pass in the @NameSubstring parameter. I know I am not dynamically building the like clause properly. How can I build the like clause when this parameter also needs to be passed as a parameter in the EXEC sp_executesql call near the bottom of the procedure?

ALTER PROCEDURE [dbo].[spGetAutoCompleteList] ( @AutoCompleteID int, @StatusFlag int, @NameSubstring varchar(100), @CompanyID int, @ReturnMappings bit, @ReturnData bit )

AS

DECLARE @ErrorCode int, @GetMappings nvarchar(500), @Debug bit, @Select AS NVARCHAR(4000), @From AS NVARCHAR(4000), @Where AS NVARCHAR(4000), @Sql AS NVARCHAR(4000), @Parms AS NVARCHAR(4000)

SET @ErrorCode = 0 SET @Debug = 1

BEGIN TRAN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON;

IF @AutoCompleteID IS NOT NULL OR @StatusFlag IS NOT NULL OR @NameSubstring IS NOT NULL
    BEGIN
        SET @Select = '
        SELECT ac.AutoCompleteID, 
               ac.AutoCompleteName, 
               ac.CompanyID, 
               ac.StatusFlag, 
               ac.OwnerOperID, 
               ac.CreateDT, 
               ac.CreateOperID, 
               ac.UpdateDT, 
               ac.UpdateOperID, 
               ac.SubmitOperID, 
               ac.SubmitDT, 
               ac.ReviewComments'

        SET @GetMappings = '
        Select ac.AutoCompleteID'

        IF @ReturnData = 1
            BEGIN
                SET @Select =  @Select + '
                    , ac.AutoCompleteData'
            END

        SET @From = '      
        FROM tbAutoComplete ac'

        SET @Where = '
        WHERE 1=1'

        IF @AutoCompleteID IS NOT NULL
            BEGIN
                SET @Where = @Where + '
                    AND ac.AutoCompleteID = CAST(@AutoCompleteID AS nvarchar)'
            END

        IF @StatusFlag IS NOT NULL
            BEGIN
                SET @Where = @Where + '
                    AND ac.StatusFlag = CAST(@StatusFlag AS nvarchar)'
            END

        IF @NameSubstring IS NOT NULL
            BEGIN
                SET @Where = @Where + '
                    AND ac.AutoCompleteName like @NameSubstring' + '%'
            END

        SET @Where = @Where + '
                AND ac.CompanyID =  + CAST(@CompanyID AS nvarchar)'

        SET @Sql = @Select + @From + @Where

        SET @Parms = '
            @AutoCompleteID int,
            @StatusFlag int,
            @NameSubstring varchar(100),
            @CompanyID int'

        EXEC sp_executesql @Sql,
                           @Parms, 
                           @AutoCompleteID,
                           @StatusFlag,
                           @NameSubstring,
                           @CompanyID

        IF @ReturnMappings = 1
            BEGIN
                SET @GetMappings = 'Select * FROM tbAutoCompleteMap acm WHERE acm.AutoCompleteID IN(' + @GetMappings + @From + @Where + ')'
                --EXEC sp_executesql @GetMappings
            END 

        IF @Debug = 1
            BEGIN
                PRINT @GetMappings
                PRINT @Sql
            END 
    END

SELECT @ErrorCode = @ErrorCode + @@ERROR

IF @ErrorCode <> 0 BEGIN SELECT '11' + 'Internal Database Error.' + '(spGetAutoCompleteList): There was an error while trying to SELECT from tbAutoComplete.' ROLLBACK TRAN RETURN END

COMMIT TRAN

Answer 1

SET @Where = @Where + 'AND ac.AutoCompleteName like ''%' + @NameSubstring + '%'''

Answer 2

So, you are asking how to specify parameters when you use dynamic queries and sp_executesql ?

It can be done, like this:

DECLARE /* ... */
SET @SQLString = N'SELECT @LastlnameOUT = max(lname) FROM pubs.dbo.employee WHERE job_lvl = @level'
SET @ParmDefinition = N'@level tinyint, @LastlnameOUT varchar(30) OUTPUT'
SET @IntVariable = 35
EXECUTE sp_executesql @SQLString, @ParmDefinition,  @level = @IntVariable,  @LastlnameOUT=@Lastlname OUTPUT

You can read more about it here: http://support.microsoft.com/kb/262499

Answer 3

@NameString needs to be outside of the quotes. To get @NameString% enclosed in quotes, you use two single quotes to escape the quote character as a literal.

        SET @Where = @Where + '
            AND ac.AutoCompleteName like ''' + @NameSubstring + '%'''

Answer 4

Perhaps this wouldn't be an issue if you weren't using dynamic SQL. It looks to me like a vanilla query would work just as well and be much more straightforward to read and debug. Consider the following:

SELECT      ac.AutoCompleteID, 
            ac.AutoCompleteName, 
            ac.CompanyID, 
            ac.StatusFlag, 
            ac.OwnerOperID, 
            ac.CreateDT, 
            ac.CreateOperID, 
            ac.UpdateDT, 
            ac.UpdateOperID, 
            ac.SubmitOperID, 
            ac.SubmitDT, 
            ac.ReviewComments
FROM    tbAutoComplete ac
WHERE   ((ac.AutoCompleteID = CAST(@AutoCompleteID AS nvarchar) OR (@AutoCompleteID IS NULL))
  AND   ((ac.StatusFlag = CAST(@StatusFlag AS nvarchar)) OR (@StatusFlag IS NULL))
  AND   ((ac.AutoCompleteName like @NameSubstring + '%') OR (@NameSubstring IS NULL))
  AND   ((ac.CompanyID =  CAST(@CompanyID AS nvarchar)) OR (@CompanyID IS NULL))

This is much simpler, clearer etc. Good luck!