By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,317 Members | 1,290 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,317 IT Pros & Developers. It's quick & easy.

Statement for returning stored procedure params

P: n/a
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

Oct 27 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Capsule (Ca******@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, es****@sommarskog.se

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

Oct 27 '05 #2

P: n/a
Thank you so much for your time, Erland! That was just what I was
looking for.

Craig

Oct 28 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.