SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 "Poll" a stored procedure		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

paulj
Starting Member

4 Posts
Posted - 10/06/2005 :  08:45:23  Show Profile  Reply with Quote
is there a way to "poll a stored procedure to determine how many parameters it needs?

What I want to do is dynamically use stored procedures and on the fly determine how many parameters it requires.

madhivanan
Premature Yak Congratulator

India
22469 Posts
Posted - 10/06/2005 :  08:50:31  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Is this?

Select parameter_name from information_schema.parameters
where Specific_name ='spname'

What is the need of this requirement?

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

ditch
Flowing Fount of Yak Knowledge

South Africa
1429 Posts
Posted - 10/06/2005 :  08:55:26  Show Profile  Visit ditch's Homepage  Reply with Quote
select specific_name, parameter_name, ordinal_position, parameter_mode, data_type
from information_schema.parameters
order by specific_name

Duane.
Go to Top of Page

paulj
Starting Member

4 Posts
Posted - 10/06/2005 :  09:08:51  Show Profile  Reply with Quote
Exactly what I needed. Thanks! Want to be able to dynamically call on stored procedures but need to know beforehand what parameters we will need to send it.
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11741 Posts
Posted - 10/06/2005 :  09:51:09  Show Profile  Visit spirit1's Homepage  Reply with Quote
note that this will require 2 trips to the db instead of just one if you know the sproc.

Go with the flow & have fun! Else fight the flow
Go to Top of Page

Kristen
Test

United Kingdom
22191 Posts
Posted - 10/06/2005 :  11:21:07  Show Profile  Reply with Quote
If you are doing this client-side there is a method in ADO to refresh the parameters for an SProc

Set oCMD = Server.CreateObject("ADODB.Command")
... set up parameters for oCMD including SProc name ...
oCmd.Parameters.Refresh

Kristen
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22469 Posts
Posted - 10/07/2005 :  01:21:37  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Kris, are you master in Client side as well?

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

Kristen
Test

United Kingdom
22191 Posts
Posted - 10/07/2005 :  01:27:48  Show Profile  Reply with Quote
Just a master in the art of Cut&Paste
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000