Statement for returning stored procedure params

[Capsule]

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

[Erland Sommarskog]

Re: Statement for returning stored procedure params

Capsule (Capsulec@hotma il.com) writes:[color=blue]
> 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.[/color]

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@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Capsule]

Re: Statement for returning stored procedure params

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

Craig