Can I query the parameters and "selected" column names from a stored procedure?

Question

I am trying to write a custom code generator that can parse stored procedures. I wrote a few regex, but they don't seem to work all the time. I do have access to the database; does a system query exist that will return the required parameters and possible return values? I have played around with sp_depends, but it does not seem to include parameters. Are there any other system procs that may be useful for this? ...am I attempting this the wrong way?

Answer 1

sp_help can provide the parameters for stored procs (along with their data types)

Answer 2

The parameters should be available in the metadata tables (I seem to recall they might even be in syscolumns against the object_id of the proc). Or the info-schemas.

However, results are tricker, since SPs don't have very formalilsed output. UDFs have much stronger metadata. The best you can do with an SP (and it is still hit'n'miss) is SET FMT_ONLY ON, and execute it and hope it doesn't use any extended stored procedures...

Answer 3

You can get the parameters from

select c.* 
from syscolumns c
inner join sysobjects o on o.id = c.id 
where o.type = 'P' and o.name = '<storedProcName>'

Return values, as Marc says, is tricky. You could have

if (...)
     select * from Customers
else
     select * from CustomerOrders

so, not going to have much success there. Of course I don't know what sort of person would write something like the above, or why, but it's possible, so...