Procedure Parameter Logging - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Procedure Parameter Logging

Author

Topic

jbenison
Starting Member

1 Post

Posted - 2005-06-11 : 18:55:15

Hello... Lets see if someone around has an idea on how to go about this.

I need to have procedure logging on each procedure, basically dump all the procedure's parameters into a log table. I have no problem getting the parameter lists and arranging them into the single string i need to send to the log table.

My main problem at the moment is how to pass the dynamic parameter values to the string. I've tried the exec but the params do not reach it, i've tought about a function but encounter the same problem, any ideas?

Thanks a lot...

Here is the code so far...

***********************************
declare @procedure_name sysname,
@ValuePairsSTR varchar(8000),
@PARAMETER_NAME varchar(8000),
@TYPE_NAME varchar(8000),
@SetStatement varchar(8000)

set @ValuePairsSTR = 'Params: '

set @procedure_name ='prcGetAllEmployeeSimple'
set @group_number = 1

DECLARE Parameter_Cursor CURSOR FOR
select
PARAMETER_NAME = c.name,
TYPE_NAME = d.type_name

from
sysobjects o,
syscolumns c,
master.dbo.spt_provider_types d,
systypes t
where
o.name = @procedure_name
and (o.type in ('P', 'TF','IF') OR (len(c.name) > 0 and o.type = 'FN'))
and o.id = c.id
and ((c.number = @group_number and o.type = 'P')
or (c.number = 0 and o.type = 'FN')
or (c.number = 1 and o.type in ('TF','IF')))
and c.xtype = d.ss_dtype
and c.length = case when d.fixlen > 0 then d.fixlen else c.length end
and c.xusertype = t.xusertype

OPEN Parameter_Cursor

FETCH NEXT FROM Parameter_Cursor into
@PARAMETER_NAME, @TYPE_NAME

set @ValuePairsSTR = @ValuePairsSTR + @PARAMETER_NAME +' ('+@TYPE_NAME+') = '+';'

WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM Parameter_Cursor into
@PARAMETER_NAME, @TYPE_NAME

set @ValuePairsSTR = @ValuePairsSTR + @PARAMETER_NAME +' ('+@TYPE_NAME+') = '+';'

END

CLOSE Parameter_Cursor
DEALLOCATE Parameter_Cursor

print @ValuePairsSTR

***********************************

Here is the result i am getting...

'Params: @p_firstName (varchar) = ;@p_lastName (varchar) = ;@p_Location (int) = ;@p_Company (int) = ;@p_profFunction (int) = ;@p_BU1 (int) = ;@p_ChkBox (char) = ;@p_ChkBox (char) = ;'

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-06-11 : 20:06:55

read thge part in red:
http://weblogs.sqlteam.com/brettk/archive/2005/01/27/4029.aspx

is this what you need?

Go with the flow & have fun! Else fight the flow

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-06-11 : 20:25:10

Still awake Mladen

?
(btw, I sent You a private mail from the forums, did it arrive?)

I'm off to bed now...
One last

--------------------------------------------------------------------------
Why don't You just log from each procedure ? (maybe I'm missing the point)

create procedure MyProc @prm1 varchar(10), @prm2 int as
-- log parameters
insert log(...) select '@prm1=' + @prm1 + ';@prm2=' + ltrim(@prm2)
-- carry on with the proc...
--------------------------------------------------------------------------

rockmoose

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources