Are input parameters accessible programatically...

Last Post 16 Jul 2007 11:49 AM by juststeve. 2 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
juststeve
New Member
New Member

--
16 Jul 2007 06:05 AM
...as opposed to being accessible by name.

In the same fashion that I can get the name of the current sproc via:
SET @ProcName = OBJECT_NAME(@@PROCID)

I'd like to get the input parameter values that were passed to a given sproc.

many thx,
--steve...
skt5000
New Member
New Member

--
16 Jul 2007 07:57 AM
This gives you all the parameters used by a procedure

SELECT DISTINCT 'Procedure'= OBJECT_NAME(object_id) ,'Parameter' = [name]
FROM sys.parameters

Jim
juststeve
New Member
New Member

--
16 Jul 2007 11:49 AM
Thankx...I'm looking to a way to get the param _values themselves.

Big picture...i'd like to devise a strategy where I can have a sproc record it's name and the values passed into it similar to what I can achieve with profiler. But I'm working with Express Edition and need to recreate this functionality.

I've found I can get the param names from within the running sproc as:

alter PROCEDURE __testing
@lastname varchar(40),
@firstname varchar(20)
AS
SELECT DISTINCT 'Procedure' = OBJECT_NAME(object_id) ,'Parameter' = [name]
FROM sys.parameters
where OBJECT_NAME(object_id) = CAST(OBJECT_NAME(@@PROCID) as varchar)
print @ProcName
GO
EXECUTE dbo.__testing 'P%', 'A%';

I have a couple hunches for how I could take this to the next stage so as to retrieve the values but I doubt I'm doing something no one else has thought to do. Perhaps someone can suggest an 'already invented wheel'.
You are not authorized to post a reply.

Acceptable Use Policy