Dereferencing T-SQL Variables

Question

Post reply

Dereferencing T-SQL Variables

Andrew Dalby

Old Hand

Points: 395
More actions
March 26, 2004 at 1:28 pm

#179157

Is it possible to dereference a variable in T-SQL? I can get the name of the variable, but I want its value.
I am the DBA for a new application in which all database access will be through audited stored procedures. The audit trail should show the procedure name along with a delimited list of parameters, their datatypes, and values. This is simplified DDL for the audit table:
CREATE TABLE audit                                    (
        audit_id   INTEGER        IDENTITY PRIMARY KEY ,
        proc_name VARCHAR   (50) NOT NULL             ,
        parameters VARCHAR (4000)                      )
GO
According to the coding standard each procedure is responsible for inserting a row into the audit table before doing anything else. Here is an example:
CREATE PROCEDURE junk
        @in_str_par1    VARCHAR (50),
        @in_dte_par2    DATETIME
AS
DECLARE @str_proc_name VARCHAR   (50),
        @str_parm_list VARCHAR (4000)
SELECT @str_proc_name = 'junk'
--This is the statement I'd like to replace
SELECT @str_parm_list =
        '@in_str_par1' + CHAR(31) +
        'VARCHAR'      + CHAR(31) +
        @in_str_par1   + CHAR(30) +
        '@in_dte_par2' + CHAR(31) +
        'DATETIME'     + CHAR(31) +
        CONVERT(VARCHAR(20), @in_dte_par2, 120)
INSERT INTO audit     (
        proc_name      ,
        parameters     )
VALUES                 (
        @str_proc_name ,
        @str_parm_list )
/*
Body of procedure
*/
GO
This is functional, but it requires a good deal of hardcoding--it's easy to forget that you added a parameter and invalidate the audit trail. I would prefer to give my programmers a template that does this automatically If I could dereference a variable, the following would simply require a single global search and replace of 'junk' with the new procedure name upon definition of the procedure.
CREATE PROCEDURE junk
        @in_str_par1    VARCHAR (50),
        @in_dte_par2    DATETIME
AS
DECLARE @str_proc_name VARCHAR   (50),
        @str_parm_list VARCHAR (4000)
SELECT @str_proc_name = 'junk',
        @str_parm_list = ''
--This is the statement I'd like to use.
SELECT @str_parm_list = @str_parm_list +
        c.name + CHAR(31) +
        t.name + CHAR(31) +
        /*Dereferenced c.name here*/ + CHAR(30)
FROM systypes   t,
        syscolumns c,
        sysobjects o
WHERE t.xtype = c.xtype
    AND c.id    = o.id
    AND o.name = @str_proc_name
ORDER BY c.colorder
INSERT INTO audit     (
        proc_name      ,
        parameters     )
VALUES                 (
        @str_proc_name ,
        @str_parm_list )
/*
Body of procedure
*/
GO
Thanks

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Antares686 SSC Guru Points: 125444 More actions · Answer 1

So were we a C programmer before?

Short and simple there is no dereferencing ability in T-SQL (at least not at this time and not in Yukon either).

You could possible use dynamic SQL to build the right string to be executed. But right off I can't see a quick way to get it too as you will still need to have the input variables involved.

Rodrigo Acosta SSC-Insane Points: 21261 More actions · Answer 2

Rodrigo Acosta

SSC-Insane

Points: 21261

March 26, 2004 at 1:44 pm

#500740

Check for sp_sproc_columns in BOL.

Andrew Dalby Old Hand Points: 395 More actions · Answer 3

I know, I know. Bad DBA! Don't select directly from the system tables. Use sp_xxx or INFORMATION_SCHEMA.xxx. Any way I do it I get the parameter name but not its value.

Andrew Dalby Old Hand Points: 395 More actions · Answer 4

Antares686,

That's what I was afraid of. BOL, MS KB, this site and Google all searched for naught. I was hoping someone knew of a magic procedure I couldn't find. I keep trying to figure out a way to package up the string so I can execute it, but the variable isn't defined in the scope of the statement, so I get an error.