时间戳

首页 > 解决方案 > 从 exec(@sql) 获取现有记录 id

sql - 从 exec(@sql) 获取现有记录 id

问题描述

ALTER PROCEDURE [dbo].[CREATE_ERROR_LOG] 
    @ERROR_MESSAGE VARCHAR(MAX) = NULL,
    @ERROR_CODE INT = 200,
    @USER_ID VARCHAR(50) = '00000',
    @TABLE_NAME VARCHAR(20),
    @CONTROLLER VARCHAR(50) = NULL,
    @METHOD VARCHAR(50) = NULL
AS
BEGIN
    DECLARE @ID AS INT = 0,
            @SQL AS VARCHAR(MAX),
    
    SET @SQL = 'SELECT @ID = ID  
                FROM ' + @TABLE_NAME + ' 
                WHERE CODE = ' + CONVERT(VARCHAR, @ERROR_CODE)
                + ' AND MESSAGE = ' + @ERROR_MESSAGE 
                + ' AND USER_ID = ' + @USER_ID 
                + 'AND CONTROLLER = ' + @CONTROLLER 
                + ' AND METHOD = ' + @METHOD;

    EXEC(@SQL);
    PRINT(@ID);

    SELECT @ERROR_CODE AS Code, @ERROR_MESSAGE AS MESSAGE;
END

我想ID从表中获取现有记录并将其存储到变量@ID中。表名作为参数发送。

我查看了几个问题，但没有找到解决方案。

标签： sqlsql-servertsqlstored-procedures

解决方案

您可以使用带有输出参数的参数化查询将@ID 值从动态查询返回到外部范围。

下面是一个带注释的示例，其中包含其他最佳实践。请注意，如果任何过滤条件参数为 ，则不会找到任何行NULL。您需要更改要检查的查询IS NULL以过滤NULL值。

CREATE OR ALTER PROCEDURE [dbo].[CREATE_ERROR_LOG] 
    @ERROR_MESSAGE VARCHAR(MAX) = NULL,
    @ERROR_CODE INT = 200,
    @USER_ID VARCHAR(50) = '00000',
    @TABLE_NAME SYSNAME, --changed data type to match identifier type
    @CONTROLLER VARCHAR(50) = NULL,
    @METHOD VARCHAR(50) = NULL
AS
BEGIN TRY
    DECLARE @ID AS INT = 0,
            @SQL AS NVARCHAR(MAX); --changed data type needed for sp_executesql

    --validate table name
    IF NOT EXISTS(SELECT 1 FROM sys.tables WHERE name = @TABLE_NAME) 
    BEGIN
        RAISERROR('Invalid table name specified: %s', 16, 1, @TABLE_NAME);
    END;

    --parameterized query and added QUOTENAME
    SET @SQL = 'SELECT @ID = ID  
                FROM ' + QUOTENAME(@TABLE_NAME) + ' 
                WHERE CODE = @ERROR_CODE
                AND MESSAGE = @ERROR_MESSAGE 
                AND USER_ID = @USER_ID 
                AND CONTROLLER = @CONTROLLER 
                AND METHOD = @METHOD;';

    --execute parameterized query with @ID as OUTPUT parameter
    EXEC sp_executesql @SQL
        ,N'@ID INT OUTPUT
          ,@ERROR_MESSAGE VARCHAR(MAX)
          ,@ERROR_CODE VARCHAR(11)
          ,@USER_ID VARCHAR(50)
          ,@CONTROLLER VARCHAR(50) = NULL
          ,@METHOD VARCHAR(50)            '
        ,@ID = @ID OUTPUT
        ,@ERROR_MESSAGE = @ERROR_MESSAGE
        ,@ERROR_CODE = @ERROR_CODE --this will implicitly convert the int to varchar
        ,@USER_ID = @USER_ID
        ,@CONTROLLER = @CONTROLLER
        ,@METHOD = @METHOD;
    
    PRINT(@ID); --local variable will remain zero if no row was found

    SELECT @ERROR_CODE AS Code, @ERROR_MESSAGE AS MESSAGE;

END TRY
BEGIN CATCH
    THROW;
END CATCH
GO

推荐阅读