CODEWITHSUNDEEP
sqlcode-generation
Nescio
Nescio

Reputation: 28423

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

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?

Upvotes: 1

Views: 111

Answers (3)

MikeW
MikeW

Reputation: 5922

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...

Upvotes: 1

Marc Gravell
Marc Gravell

Reputation: 1062755

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...

Upvotes: 0

Learning
Learning

Reputation: 8175

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

Upvotes: 0

Related Questions