how to store parameter values and parameter names?

[bluecjh]

declare @test varchar(1000);
set @Test = '';
SELECT    
	@Test = @Test + ',' + c.name
FROM      
	syscolumns c
	INNER JOIN sysobjects O On c.id = o.id
WHERE     
	o.id = @@procid

select @Test

with the above i can get and store @parameter names
from a procedure, is there a way to combine
the parameter values which i want to store and
associated witht the parameter names, i'm stumped

BlueCJH

 

[AlexCuse]

I don't think you can store parameter values in this fashion, the whole purpose of using parameters is that you can supply the values at run time.

Are you trying to log this information each time your SP executes? If so, you probably could just add a query like this (provided your log table exists)

insert into LogTable
select 'ProcName', '@ParamName', @ParamName

This will insert the ProcName and parameter name (hard coded by you), followed by the parameter value (which can vary). Is this what you are looking to do?

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[bluecjh]

Alex
Well i want this to run generically within
a lot of stored procedures. I don't want
to have to hard code stored procedure
names and parameters

I was wondering if pass the parameter name as
a string whether sp_executeSql in the function
would be able to retrieve the value but
sp_executeSql won't run in a function.

SELECT    
	@Test = @Test + ',' + c.name + dbo.udfMyTest(c.name)
FROM      
	syscolumns c
	INNER JOIN sysobjects O On c.id = o.id

WHERE     
	o.id = @@procid

but it's a start

BlueCJH

 

[AlexCuse]

If the query you posted works you simply need to add cast(@param as varchar) to the select list and concatenate it to your string. However, you will need to add the query to each proc. I think that anything that you could call generically would be just as much work, because you would need to pass it the SP name and all the parameters (plus you would need to write your procedure to handle for LOTS of different possibilities).

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom