How can I use SQL to get a variable value from a literal?

Question

Part 1

DECLARE @A INT
DECLARE @B NVARCHAR(20)

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

Part 2

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

EXEC SP_EXECUTESQL @SQL
--Should return 123

Directly referencing @A in non-dynamic SQL would not be acceptable for this task.

The above in Part 2 is generically what I am trying to do. I understand the variable is out of scope and it won't work as done above. How could I use @B to get the value of @A?

UPDATE 20190322: I actually forgot about this question and implemented logging on the C# side instead of the database, but I got curious again if this was possible. Again, this needs to be generic as I would want to put it into the tops of any stored procedure and I do not want to customize it per sproc; I'm having trouble in the cursor getting the value of a parameter. Here is a working example:

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

I didn't understand the whole thing you are asking, but you can define the variables on sp_executesql:

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

Answer 2

After searching around, I found that one can

1. Have the running procedure code from dm_exec_requests & dm_exec_sql_text

2. Have the procedure call from dm_exec_input_buffer. I should note that I am not very proficient in this, and I think I read somewhere it does not work on queries done outside SSMS....

3. As already seen in your code, the parameters can be found in sys.parameters

Thus, I implemented a way to do this. Main algorithm steps are:

1. Create a dummy proc with same params and default values. Change its body to a single SELECT. By using a dynamic xml-concatenation of the parameters at the original proc which is one level higher, we can provide the dummy proc a VALUES table which includes both the parameter names (in quotes) and their values(no quotes).
2. Exec the dummy proc by using the same call parameters used in the original spc. INSERT the EXEC result-set into a temp table. Drop the dummy proc afterwards.

Now we have a temp table with both parameter names AND values.

Most of the new code is before yours starts, except from the actual value you need in the cursor. Also, to avoid furious parsing, I used a (cheap) trick of adding a comment --postBeginParserMarker just after the BEGIN so that I know where the proc begins...

alter PROCEDURE [dbo].[LoggingTest]
    @DateBegin datetime,
    @DateEnd datetime,
    @Person varchar(8000),
    @Unit varchar(8000)='someunithere'
AS
BEGIN --postBeginParserMarker
    declare @SPID int= @@SPID;
    declare @request_id int;
    declare @dummyspc nvarchar(max)
    select @dummyspc = 'spc_dummy_'+ convert(nvarchar,max(object_id)+1) from sys.procedures -- just a way to ensure no conflicts between simultaneous runs of this very proc
    --select @dummyspc

    declare @thisprocname sysname
    select @thisprocname=o.name,@request_id=request_id
    from sys.dm_exec_requests r
    cross apply sys.dm_exec_sql_text(r.sql_handle) t
    inner join sys.objects o on t.objectid=o.object_id
    where r.session_id=@SPID
    --select @thisprocname

    declare @newproc nvarchar(max)
    SELECT @newproc=substring(st.text,1,CHARINDEX ('--postBeginParserMarker' , st.text)-1 )
    FROM sys.dm_exec_requests r
    CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) st
    where r.session_id=@SPID
    set @newproc=replace(@newproc,@thisprocname,@dummyspc)

    SELECT @newproc+=
         char(13)+char(10)
        +'select * from ( values'
        +STUFF
        (
            (    
                SELECT char(13)+char(10)+char(9)+',('+convert(nvarchar,parameter_id)+','''+name+''',convert(nvarchar,'+name+'))'
                from sys.parameters 
                where object_id=object_id(@thisprocname)
                FOR XML PATH('') , TYPE
            ).value('.','nvarchar(max)')
            , 4, 1, ' ' 
        )
        +char(13)+char(10)+')t(parameter_id,parameter_name,parameter_value)'
        +char(13)+char(10)+'END'
    --select @newproc

    declare @newproccall nvarchar(max)
    select @newproccall=event_info from sys.dm_exec_input_buffer ( @SPID ,@request_id)
    set @newproccall=replace(@newproccall,@thisprocname,@dummyspc)
    --select @newproccall

    exec(@newproc)

    if object_id('tempdb..#paramtbl') is not null drop table #paramtbl
    create table #paramtbl (parameter_id int,parameter_name nvarchar(max),parameter_value nvarchar(max))
    insert #paramtbl(parameter_id,parameter_name,parameter_value)
    exec(@newproccall)

    -- select * from #paramtbl  <-- Now this has all you need

    exec('drop procedure '+@dummyspc)

    --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);  

    --select @Unit as val
    --drop table ##a 
    --select @@SPID


    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('LoggingTest')

    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 isnull(parameter_value,'<NULL>') from #paramtbl where parameter_id=@Param_order)

        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