title: How do I dynamically build a like clause in an executable sql stored procedure that uses EXEC sp_executesql?
body: <p>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?</p>
  
  <pre><code>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 &lt;&gt; 0 
      BEGIN
          SELECT '&lt;FaultClass&gt;1&lt;/FaultClass&gt;&lt;FaultCode&gt;1&lt;/FaultCode&gt;' 
                  + '&lt;FaultDesc&gt;Internal Database Error.&lt;/FaultDesc&gt;'
                  + '&lt;FaultDebugInfo&gt;(spGetAutoCompleteList):  There was an error while trying to SELECT from tbAutoComplete.&lt;/FaultDebugInfo&gt;'
          ROLLBACK TRAN
          RETURN
      END
  
  COMMIT TRAN
  </code></pre>