How do I isolate an SP''s parameter values inside the SP generically? Please read
-
Hi:
I am trying to create a process that can be plugged into any Stored Proc without modification, which will trap the values of the SP's parms without explicitly knowing their names or how many there are. It's easy to get the names and count - I wrote a function that returns the names of the parms for the given SP in a table. But then getting the VALUES of the parms, I can't figure out. When I refer to an entry in my returned table which has the name of one of the parms, SQL doesn't know what I'm talking about - to it, this is just a string, not the parm name. EG, if I have
CREATE PROCEDURE sp_ABC @def int, @Ghi varchar(100)
then my function returns a table with 2 rows, @abc and @Ghi, and I try to query @abc for its value, SQL thinks I've just introduced a new variable without declaring it.
Can someone help? I would much appreciate it...
Wayne Kelley
-
remember that a stored proc is really just a formula, just like A2 + B2 = C2;
A,B and C would have a datatype you can discover, but they do not have values, so they can't be discovered.
you can use sp_depends procname to find the tables/columns referenced, and sp_help procname will give you the paramters, their data types, and even their order.
do you mean default value? like this?
CREATE PROCEDURE sp_ABC @def int=0, @Ghi varchar(100)='Administrator'
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Use DBCC InputBuffer(@@SPID) to find out the string that called the procedure and then parse out the values. For example:
Create
Procedure dbo.TestParmDetection
@Parm1
int,
@Parm2
varchar(5)
As
DBCC
InputBuffer(@@SPID)
Go
Exec
dbo.TestParmDetection 1, 'abcdef'
My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url] -
Hi!
"I am trying to create a process that can be plugged into any Stored Proc without modification"
I don't understand exactly what you are asking for; which of these procedures are you asking for:
1. one you can run while there is "any Stored Proc" running so you can see the parameters passed in?
2. one you can run while there is "any Stored Proc" running so you can see the current parameter values?
3. one you can execute WITHIN "any Stored Proc" to see the current/passed in parameters?
Or have I missed the point totally?
Also check out this article, it might help you on your way http://www.databasejournal.com/features/mssql/article.php/2189761 -
As mentioned above sp_help will get you the SP's parameters but generically obtaining their values is another issue. DBCC InputBuffer(@@SPID) (also mention above) would get you the values of the explicitly coded parameters if they were constants but if they were variables or not explicitly coded... well...
Maybe a little explanation of why this is necessary would help in formulating an alternative solution !
PeteK
I have CDO. It's like OCD but all the letters are in alphabetical order... as they should be. -
Wayne, I think you are trying to insert a uniform piece of code into each stored procedure that will figure out what the parameters and values are that were passed to the sproc.
There is a good deal of reflection in SQL server, but it's not to the same level as an object-oriented language.
There may be a way to accomplish what you would like to do, but the cost is prohibitive, in my opinion. If you need this kind of metadata about the system you are working on, you might be best served by using the SQL profiler.
But here's something that you can play with if you choose to persist:
Create Procedure WTF (@p1 int) AS
EXEC('DBCC INPUTBUFFER(@@SPID)')
GO
WTF @p1=-1000000
jg
-
Thanks for all the suggestions, I learned something from each one of them. I ended up using the DBCC InputBuffer command.
To clarify, yes, I was trying to capture the names and values of all the parms passed into a proc. I wanted it to be generic, so you could add a minimum number of lines to each proc in a DB, and you wouldn't have to worry about the details of any given proc. The end product is to enter a row in a logging table that contains the proc name, execution string, start time, and end time. The purpose is to identify poor-performing SPs. So a given row in the logging table might have the following columns:
Proc name, exec string, start time, end time
with values
"sp_test", "exec sp_test 'Billy Joe Jones', 23", 2007-02-08 12:01:01.333, 2007-02-08 12:02.01.333
where the SP name is sp_test, the parms to the SP are a text string 'Billy Joe Jones' and an integer value of 23, and the start and end times indicate that the SP took 1 minute to run (way too long).
My ultimate goal is to develop a way to insert the pertinent lines of code into all the procs on a batch basis, perhaps to a text script file. IE, the text file would contain the scripted-out versions of all the procs, or maybe a separate text file for each proc, and I would globally insert the lines to each proc in one fell swoop. Then I could drop and recreate each proc in the DB, with the new code in place.
I will post my solution later today, but if anyone has a really elegant solution I'd love to see it.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply