Microsoft SQL Server forum - Statement for returning stored procedure params

Author

Statement for returning stored procedure params

Capsule

2005-10-27, 5:28 pm

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

2005-10-27, 5:28 pm

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

Capsule

2005-10-27, 8:23 pm

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

Craig

Sponsored Links