Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
leeholden
Starting Member
34 Posts |
 Posted - 2002-03-26 : 12:50:33
|
| Is there any why I can programatically return the required parameters for a user defined stored procedure?i.e.Create procedure test_proc @param1 integer, @param2 varchar = '', @param3 varchar = '' outputas<SQL CODE>Some SQL that doesn't involve parsing the output of "sp_helptext test_proc" would be ideal, but for now I want to do it in Visual Basic and populate an array that gives me the parameter name, and preferably the data type, the default value and if it is an output parameter (but just the name will do for the time being). |
|
|
Onamuji
Aged Yak Warrior
504 Posts |
Posted - 2002-03-26 : 12:51:54
|
| I'm pretty sure the syscolumns table has the information your looking for. If not I know it's in one of the system tables.. |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-03-26 : 12:57:55
|
| There's also the INFORMATION_SCHEMA.PARAMETERS view, which lists everything from syscolumns in a more easy-to-read format.You could also use the ADO Command object; it has a Parameters collection, and this collection has a method called Refresh. It will populate all of the parameters for the stored procedure. This is probably the easiest method, and it's well documented both in the ADO docs and on most ASP sites:www.4guysfromrolla.comwww.aspfaqs.comwww.15seconds.comwww.aspalliance.comwww.asp101.comIt's not a good practice to use in a production application however, because it adds network and processing overhead (you have to query the SQL Server to get the parameters, then hit it again to run the procedure). I know I'm gonna sound like a cranky old bastard for saying this, but you shouldn't be calling a stored procedure if you don't know the parameters it accepts. My 2¢ |
|
|
|
leeholden
Starting Member
34 Posts |
Posted - 2002-03-26 : 13:01:05
|
| It is in syscolumns. Thank You.Didn't think of looking in there.I can get the name and datatype from there but not so sure about default value (i.e. is it compulsary or optional?) or if it is an OUTPUT parameter? By the way, any idea what the difference is between xtype and type in syscolumns? |
|
|
|
leeholden
Starting Member
34 Posts |
Posted - 2002-03-26 : 13:05:57
|
robvolk,quote:
you shouldn't be calling a stored procedure if you don't know the parameters it accepts
how many times have you asked the guy sitting next to you which order the parameters are in, or what the parameter name is for a particular proc? I agree that you shouldn't call a stored procedure if you don't know what it does, but just because you can't remember the name of one of the parameters!? |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-03-26 : 15:05:26
|
quote:
how many times have you asked the guy sitting next to you which order the parameters are in, or what the parameter name is for a particular proc?
None! I've written 99% of the procs I ever used, and if I didn't, I made sure to check the source code before I used it (I never rely on someone being available to answer my questions about a sproc; what happens when they're not there?) Also, I use named parameters so I don't have to worry about the order, and by extension, I know the name! The only time I don't use named parameters is when the sproc only has 1 or 2.quote:
I agree that you shouldn't call a stored procedure if you don't know what it does, but just because you can't remember the name of one of the parameters!?
Absolutely! I'm assuming that you have the source code for the sproc available, or that you created it and would know them. If I'm calling an SP either from a client app or from query analyzer, I check the source first and foremost and always. Takes 2 seconds and it tells me everything I need.I've never worked on a large team where I might not have access to the source code. To be honest, I could never work in an environment like that; it would be like driving blindfolded while someone whispers directions in my ear.Told ya I'd sound like a cranky old bastard!  |
|
|
|
Jay99
468 Posts |
Posted - 2002-03-26 : 15:17:06
|
quote:
...I've written 99% of the procs I ever used...
You are so lucky, you don't even know . . .The last two contracts I have been on, 90% of the procs where written by (not-so-talented) front-end developers.Not only do I 'drive blindfolded', but the guy whipering in my ear smells like 3 day old egg salad.  Jay<O> |
|
|
|
|
|
|
|
|
Stored Procedure Parameters