Reflection on Stored Procedure

  • Thread starter Thread starter Aragorn
  • Start date Start date
A

Aragorn

I want to load into a SqlCommand objet an existing stored
procedure. I know that by using the Parameters collection
I can add parameters, but how can I retrieve the
parameters already defined from the SP? I would like by a
reflection-like mechanisms be able to get the parameters
declared into the SP.

Strangely, if you're using the Server Explorer and you
drag a stored procedure on your page, inside the
automatic generated code the parameters will be set
correctly. But since I want to use a dynamic mechanism to
load a stored and retrieve its parameters, I can't choose
in advance a stored procedure and drag it on my page.

Gandalf says hi.

Thank you for any help,
Aragorn
 
Aragorn,

I believe that you can get the parameters by using the sp_sproc_columns
stored procedure. It will return a result set with the parameters for the
stored procedure. You will have to do a little filtering, but it will give
you all the information you want.

What is returned is shown in the documentation for the sp_sproc_columns
procedure.

Hope this helps.
 
Hi,

After a quick test, it seems that I will be able to
retrieve the parameters associated to my stored proc.
Thank you: you made my day!

Regards,
A
-----Original Message-----
Aragorn,

I believe that you can get the parameters by using the sp_sproc_columns
stored procedure. It will return a result set with the parameters for the
stored procedure. You will have to do a little filtering, but it will give
you all the information you want.

What is returned is shown in the documentation for the sp_sproc_columns
procedure.

Hope this helps.


--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

Aragorn said:
I want to load into a SqlCommand objet an existing stored
procedure. I know that by using the Parameters collection
I can add parameters, but how can I retrieve the
parameters already defined from the SP? I would like by a
reflection-like mechanisms be able to get the parameters
declared into the SP.

Strangely, if you're using the Server Explorer and you
drag a stored procedure on your page, inside the
automatic generated code the parameters will be set
correctly. But since I want to use a dynamic mechanism to
load a stored and retrieve its parameters, I can't choose
in advance a stored procedure and drag it on my page.

Gandalf says hi.

Thank you for any help,
Aragorn
Click to expand...


.
Click to expand...
 
Back
Top