sql-server - 如何在使用 EXEC sp_executesql 的可执行 sql 存储过程中动态构建 like 子句？

Question

当我传入@NameSubstring 参数时，以下存储过程可以正常执行。我知道我没有正确地动态构建 like 子句。当此参数还需要作为参数传递到过程底部附近的 EXEC sp_executesql 调用中时，如何构建 like 子句？

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 '<FaultClass>1</FaultClass><FaultCode>1</FaultCode>' 
                + '<FaultDesc>Internal Database Error.</FaultDesc>'
                + '<FaultDebugInfo>(spGetAutoCompleteList):  There was an error while trying to SELECT from tbAutoComplete.</FaultDebugInfo>'
        ROLLBACK TRAN
        RETURN
    END

COMMIT TRAN

Answer 1

@NameString 需要在引号之外。要将 @NameString% 括在引号中，您可以使用两个单引号将引号字符转义为文字。

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