title: How can I use SQL to get a variable value from a literal?
body: <p>Part 1</p>
  
  <pre><code>DECLARE @A INT
  DECLARE @B NVARCHAR(20)
  
  SET @A=123
  SET @B='@A'
  </code></pre>
  
  <p>Part 2</p>
  
  <pre><code>DECLARE @SQL NVARCHAR(MAX)
  SET @SQL='SELECT ' + @B
  
  EXEC SP_EXECUTESQL @SQL
  --Should return 123
  </code></pre>
  
  <p><strong>Directly referencing @A in non-dynamic SQL would not be acceptable for this task.</strong></p>
  
  <p>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? </p>
  
  <p><strong>UPDATE 20190322</strong>:
  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:</p>
  
  <pre><code>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
  </code></pre>