Statement for returning stored procedure params

Hi all,

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.

Thanks in advance,

Craig

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/techin.../2000/books.asp

Thank you so much for your time, Erland!   That was just what I was
looking for.

Craig