December 27, 2002 at 6:05 am
Hello,
I want to make a generic piece of code that loops through the parameters of a stored procedure and prints the name and value of the parameter.
example.
SPName: sp_test
param1 - param1 value
param2 - param2 value
param3 - param3 value
This code is called from within a stored procedure to provide me with some parameter info ...
I don't want to achieve this by simply concatenating a string for each SP, because then I have to write different code for each of the 100+ stored procedures in my database.
Is this possible ??
Thanks a lot
Yoeri
December 27, 2002 at 11:36 am
As far as getting the parameters, that's easy, grab the text from the system table containing the stored proc, and using the structure of Create Proc Name param1,param2,param3 AS you can grab everything up to the AS keyword (find it with Charindex) and then break out the parameters by looking for the commas as seperators between the name and the AS keyword. As far as the values, I'm not sure what your asking. If it's the value type, you've gotten it already by parsing the text. If it's values to match what should go in the proc as an actual working value of data, I don't know how you could possibly get that without commenting ranges in the procs and parsing it, or using generic functions to generate values based on type you parsed, which I don't believe would be too useful unless much of the data in your field types shared ranges. For example an int, throwing a four digit int (1,000's) into something expecting a five digit (10,000's) could cause all kinds of unexpected results.
December 27, 2002 at 11:40 am
Just run this and enjoy:
select 'Procedure Name' = o.name,
'Parameter_name' = c.name,
'Type' = type_name(c.xusertype),
'Length' = c.length,
'Prec' = case when type_name(c.xtype) = 'uniqueidentifier' then c.xprec
else OdbcPrec(c.xtype, c.length, c.xprec) end,
'Scale' = OdbcScale(c.xtype, c.xscale),
'Param_order' = c.colid,
'Collation' = c.collation
from syscolumns c
join sysobjects o
on o.type = 'P'
and o.id = c.id
order by o.name
December 27, 2002 at 12:54 pm
Along the same lines, but more from a report building side:
-- Variables to use for cycling through the stored procedures
-- Identified from sysobjects
DECLARE @procName sysName,
@procID int
-- Cursor with the pertinent information for all the
-- stored procedures in the database
DECLARE cursProcs CURSOR FAST_FORWARD
FOR
SELECT name, id
FROM sysobjects
WHERE xtype = 'P'
-- Open the cursor
OPEN cursProcs
-- Prime the pump
FETCH NEXT FROM cursProcs INTO @procName, @procID
-- Temporary table to store the results
CREATE TABLE #ProcResults (
LineID int IDENTITY,
LineInfo varchar(255)
)
-- Cycle through the stored procedures
WHILE (@@FETCH_STATUS = 0)
BEGIN
-- Stored Procedure Name
INSERT INTO #ProcResults (LineInfo) VALUES ('SPName: ' + @procName)
-- All the parameters in the order they are called
INSERT INTO #ProcResults (LineInfo)
SELECT ' ' + sc.name + ' ' + CASE
WHEN sc.xusertype
IN (127, 104, 61, 34, 56, 58, 52, 35, 189, 48, 38, 256, 36)
THEN st.name
WHEN sc.xusertype
IN (173, 175, 239, 99, 231, 98, 165, 167)
THEN st.name + '(' + CAST(sc.length AS varchar) + ')'
ELSE
st.name + '(' + CAST(sc.xprec AS varchar) + ', '
+ CAST(sc.xscale AS varchar) + ')'
END
FROM syscolumns sc
JOIN systypes st
ON sc.xusertype = st.xusertype
WHERE id = @procID
ORDER BY colorder
-- Spacer Line
INSERT INTO #ProcResults (LineInfo) VALUES ('')
FETCH NEXT FROM cursProcs INTO @procName, @procID
END
CLOSE cursProcs
DEALLOCATE cursProcs
SELECT LineInfo
FROM #ProcResults
ORDER BY LineID
DROP TABLE #ProcResults
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
December 27, 2002 at 7:01 pm
I like that!!!!!
December 30, 2002 at 1:24 pm
Ok thanks ...
Now, I have another problem...
I already have the names, but can I get the value of a parameter by its name ?
December 30, 2002 at 1:48 pm
What do you mean? What value of a parameter?
If you are talking about order of parameters, then there is a column called Param_order in the SELECT statement I provided. It is actually read from the column syscolumns.colid.
Edited by - mromm on 12/30/2002 2:01:26 PM
December 30, 2002 at 2:07 pm
Yes. If you've called the stored procedure and passed it a parameter, you can reference it like any other variable from within the stored procedure. For instance:
CREATE PROC dbo.TestProc
@Value int
AS
SET NOCOUNT ON
SELECT @Value [@Value]
GO
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
December 30, 2002 at 4:24 pm
I think what Yoeri is getting at is the ability from within the procedure, to print out the names and values of each parameter as it was passed to the proc at runtime. The bad news is, I'm not aware of a way to do it. I think you're looking for something like the "$#" and "$n" arguments in Korn shell or the "%1"..."%9" parameters in DOS which allow you to iterate through each of the parameters passed to the procedure.
I tried doing this using Dynamic SQL, but the proc parameters are not valid in the context that is executing the dynamic SQL.
This isn't valid:
declare @x int
exec('PRINT @x')
-Dan
-Dan
December 30, 2002 at 7:12 pm
Not quite what you're looking for but one way to do it would to write a tool that would run through all your procs and add explicit code to dump the params, either as prints or writing to a table/file/etc. This is really not a bad approach since it doesn't seem like you'd want this code active all the time, you could then remove it when done. I'd put in a special header/footer to make it easy to manage:
--START PARAM DUMP
blah
blah
blah
--END PARAM DUMP
Andy
December 31, 2002 at 8:22 am
As far as getting the parameter names and datatypes you could just select from information_schema.parameters rather than query the system tables directly.
December 31, 2002 at 8:28 am
In SQL Server 2000, yes. In SQL Server 7, no. The Parameters Information Schema view is one of three new such IS views to SQL Server 2000.
http://qa.sqlservercentral.com/columnists/bkelley/informationschemaviews.asp
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
January 2, 2003 at 12:41 am
dj_meier, that is exactly what I want to do, as I stated in my first post.
I want to get a list like ...
Param1 = value1
Param2 = value2
at runtime ...
January 6, 2003 at 9:23 am
just out of curiosity, what is the purpose behind this exercise?
I admit, its an intruiging prospect that has me stumped, and I'd love to see a solution or readh my own solution for this idea, but i'm still curious as to its application.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply