CODEWITHSUNDEEP
sql-servert-sqldynamic
UnhandledExcepSean
UnhandledExcepSean

Reputation: 12804

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

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

Upvotes: 4

Views: 549

Answers (4)

George Menoutis
George Menoutis

Reputation: 7240

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

Upvotes: 1

Vladimir Baranov
Vladimir Baranov

Reputation: 32685

Starting from SQL Server 2014 SP2 there is a sys.dm_exec_input_buffer dynamic management view.

In previous versions there is DBCC INPUTBUFFER.

It returns a table, which contains a column event_info, which contains "The text of the statement in the input buffer for the given spid."

So, you can add a simple query to the start of your stored procedure, something like this:

INSERT INTO LoggingTable(event_info, event_type, parameters)
SELECT
    InBuf.event_info
    ,InBuf.event_type
    ,InBuf.parameters
FROM
    sys.dm_exec_requests AS Req
    INNER JOIN sys.dm_exec_sessions AS Ses ON Ses.session_id = Req.session_id
    CROSS APPLY sys.dm_exec_input_buffer(Req.session_id, Req.request_id) AS InBuf
WHERE
    Ses.session_id > 50 
    AND Ses.is_user_process = 1
;

If you call/run your stored procedure using EXEC, such as

EXEC [dbo].[LoggingTest]
@DateBegin = '2019-01-01',
@DateEnd = '2020-01-01',
@Person = 'person name',
@Unit = 'some unit';

Then, event_info will contain this exact text of the EXEC query. I mean, exactly this text above. EXEC [dbo].[LoggingTest] @DateBegin = '2019-01-01', @DateEnd = '2020-01-01', @Person = 'person name', @Unit = 'some unit'; Obviously, it contains values of parameters and there is no need to parse anything or do any other tricks. It is enough for logging.

Cool. Event type will be "Language Event".

Unfortunately, if you call your stored procedure "properly" using RPC, which usually happens in, say, C# code, then all you'll get is just the name of the stored procedure in the event_info. Something like this: 

YourDatabaseName.dbo.LoggingTest

There will be no mentioning of parameters and/or their values :-( Event type will be "RPC Event".

The parameters column is smallint and always contained 0 in my tests. Docs are not clear on how to make sense of this.

Upvotes: 1

Lamak
Lamak

Reputation: 70638

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

Upvotes: 5

Sonam
Sonam

Reputation: 3466

Though you don't have to do this, but still here is the way:

SET @B=@A

And you can directly assign value of @A:

 DECLARE @SQL NVARCHAR(MAX)
 SET @SQL='SELECT ' + Convert(varchar(50),@A)

Upvotes: 1

Related Questions