Posted by Erland Sommarskog on 09/29/99 11:30

Capsule (Capsulec@hotmail.com) writes:
> I'd like to put together a SQL statement that will take the name of a
> stored procedure as a param, and return that SP's parameters.
>
> I'm writing a test application, and I'd like to wrte a generator to
> save myself some time, but I can't seem to figure out how to get the
> params from a SP. Any help would be appreciated.

I happened to have this one canned:

SELECT name = CASE colid WHEN 0 THEN NULL ELSE name END,
paramno = colid, type = type_name(xtype),
max_length = length, "precision" = coalesce(prec, 0),
scale = coalesce(scale, 0),
is_input = CASE colid WHEN 0 THEN 0 ELSE 1 END,
is_output = CASE colid WHEN 0 THEN 1 ELSE isoutparam END,
is_retstatus = 0
FROM dbo.syscolumns
WHERE id = @objid
UNION
SELECT NULL, 0, 'int', 4, 0, 0, 0, 1, 1
WHERE NOT EXISTS (SELECT *
FROM dbo.syscolumns
WHERE id = @objid
AND colid = 0)
ORDER BY paramno



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Back to original message]