sql-server - 如何使用 SQL 从文字中获取变量值？

Question

第 1 部分

DECLARE @A INT
DECLARE @B NVARCHAR(20)

SET @A=123
SET @B='@A'

第 2 部分

DECLARE @SQL NVARCHAR(MAX)
SET @SQL='SELECT ' + @B

EXEC SP_EXECUTESQL @SQL
--Should return 123

此任务不接受在非动态 SQL 中直接引用 @A。

第 2 部分中的上述内容通常是我正在尝试做的事情。我知道该变量超出了范围，并且它不会像上面那样工作。我如何使用@B来获取@A的值？

更新 20190322:我实际上忘记了这个问题，并在 C# 端而不是数据库上实现了日志记录，但我再次好奇这是否可能。同样，这需要是通用的，因为我想将它放入任何存储过程的顶部，并且我不想为每个存储过程自定义它；我在光标获取参数值时遇到问题。这是一个工作示例:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO    

ALTER PROCEDURE [dbo].[LoggingTest]
    @DateBegin datetime,
    @DateEnd datetime,
    @Person varchar(8000),
    @Unit varchar(8000)
AS
BEGIN
    --BEGIN LOGGING CODE
    DECLARE @String NVARCHAR(MAX)=''
    DECLARE @Parameter_name nvarchar(2000), @type nvarchar(50), @length SMALLINT, @Prec SMALLINT, @Scale SMALLINT, @Param_order SMALLINT, @Collation nvarchar(2000);  
    DECLARE param_cursor CURSOR FOR   
        SELECT
           'Parameter_name' = name,  
           'Type'   = type_name(user_type_id),  
           'Length'   = max_length,  
           'Prec'   = case when type_name(system_type_id) = 'uniqueidentifier' 
                      then precision  
                      else OdbcPrec(system_type_id, max_length, precision) end,  
           'Scale'   = OdbcScale(system_type_id, scale),  
           'Param_order'  = parameter_id,  
           'Collation'   = convert(sysname, 
                           case when system_type_id in (35, 99, 167, 175, 231, 239)  
                           then ServerProperty('collation') end)  
        from sys.parameters
        where object_id = object_id(OBJECT_NAME(@@PROCID))

    OPEN param_cursor  

    FETCH NEXT FROM param_cursor   
    INTO @Parameter_name,@type,@length,@Prec,@Scale,@Param_order,@Collation

    WHILE @@FETCH_STATUS = 0  
    BEGIN  
        SET @String=@String + @Parameter_name + '==' --+ SELECT @Parameter_name --This is part I can't think of a way to do; need to record/capture the value

        SET @String=@String + CHAR(13) + CHAR(10)
        FETCH NEXT FROM param_cursor   
        INTO @Parameter_name, @type,@length,@Prec,@Scale,@Param_order,@Collation
    END   
    CLOSE param_cursor;  
    DEALLOCATE param_cursor;

    --INSERT DATA INTO LOG TABLE HERE
    SELECT OBJECT_SCHEMA_NAME(@@PROCID) + '.' + OBJECT_NAME(@@PROCID) AS [ProcedureName],@String AS [Data],GETDATE() AS [LogTime]
    --END LOGGING CODE    

    /* !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
        DO BUSINESS STUFF HERE!
        !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! */
    /*
    BLAH
    BLAH
    BLAH

        DECLARE @Now DATETIME=GETDATE()
        EXEC [dbo].[LoggingTest] @Now,@Now,'Person Value','Unit Value'
    */
END
GO

Answer 1

我不明白你所问的全部内容，但你可以在 sp_executesql 上定义变量:

EXEC SP_EXECUTESQL @SQL, N'@A INT', @A = @A