Dereferencing T-SQL Variables
-
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
- --This is the statement I'd like to replace
-
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.
-
Check for sp_sproc_columns in BOL.
-
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.
-
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.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply