Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 204
  • Last Modified:

Query a table with a list of stored procedures and execute that SP within another SP by passing SP Name and parameteres of it

Is it or would be possible to create stored procedure that could have a list of parameters to pass the stored procedure name to run and the parameters it needs to run it?

I need to create a table to define a list of stored procedure names, their parameter names, and the parameter types within a table. As the plan is to call the specified SP from a query to select another SP from the list based on certain selection criteria. Is this even possible and if so how would I go about this? Would there or could there be any possible bottle necks from doing this?
0
sweoff
Asked:
sweoff
  • 5
  • 3
  • 2
  • +2
4 Solutions
 
UnifiedISCommented:
Possible = yes, but fairly complex. What is the user interface platform you will be using?

You can get the parameters/data types  from INFORMATION_SCHEMA.PARAMETERS.
0
 
Scott PletcherSenior DBACommented:
Yes, it's definitely possible, but it would almost certainly require dynamic SQL.

As to params, SQL has a sys.parameters table that provides the params for you.  All you need to do is piggy-back off of that.
0
 
sweoffAuthor Commented:
I thought this was possible.. The table that will hold the SP Names will probably have another related table that will be 1 to many relation table...  and will hold the parameters and data types for those sp's. Also each of the SP's listed within that table will already be created.. It will be a separate SP that will execute those based on the passed sp name and parameters that I want to pass into it. Can anyone help me with a jump start on how I would code that master SP to take the parameters that will call the specified SP and its parameters?
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
UnifiedISCommented:
One method would be to pass all the parameters as a delimited string that you would then need to parse within the SP, something like this:
'@Param1=123,@Param2=ABC'
0
 
UnifiedISCommented:
Another thought would be to store the parameter name and values in a table with a run ID then pass the run ID to the proc so it can retrieve the parameters and values and build the dynamic sql. Each RunID would represent a request to run the procedure.  
if you are building an application, it would be easier than having a generic master stored procedure.  What application will be making the calls to SQL server and allowing the users to choose the stored procedure/set the values?
0
 
sweoffAuthor Commented:
its going to be a webservice and the service will have an object that is passed within it that would be an available list of the SP's that application that calls the webservice would use to get it to call what ever specific data it needed.
0
 
sweoffAuthor Commented:
So I looking to make an all in-compassing Web Service basically with ever having to write additional calls for the for the service. They would get a dynamic SP list from the object that's passed within the service to specify what they need or want,
0
 
Walter PadrónCommented:
A webservice is a CONTRACT between you and the clients of the service, so once you expose an sproc + parameters hardly you can change them later without breaking the clients. What if you later wants another database system, or added more parameters?
I will instead in the webservice expose a method for your sproc and then in the webservice code do whatever you like, build a dynamic SQL string or  call the sproc directly. It also made the webservice more discoverable.

Best regards
0
 
sweoffAuthor Commented:
Walter-- yes I understand what your saying and that's why I'm building Web Service Object (DLL) that will contain the exposed generic/dynamic calls that the object will have to expose the list Of object types that if and when the developers need to add additional SP calls then they add the SPs in to the table with relation to the parameters table to expose new calls as they needed in the future. I'm building this for a portal that will have the ability for junior level developers to easily call functions within  the portal to make their portal applications work without stubbing their project environment  with Web references and reducing advanced concepts on them for have the whole process of serialize and desterilize for security purposes.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Did you think about Stored Procedure grouping?
It's a solution that let you have as many SP's as you want and all with the same name and each one it's work independently so a SP can has different parameters.
Example:
CREATE PROC MySPName;1
AS SELECT * FROM TableA
GO
CREATE PROC MySPName;2
AS SELECT id, name FROM TableB
GO
CREATE PROC MySPName(@ID INT);3
AS SELECT * 
FROM TableA
INNER JOIN TableB ON TableA.id=TableB.id
WHERE TableA.id=@ID
GO

Open in new window


Then you'll call each SP with the correspondent number. Example for the 3: EXEC MySPName(999);3
0
 
sweoffAuthor Commented:
Thanks Guys
0
 
Walter PadrónCommented:
@sweoff if i understand it will work in this way from the client
- First calls your webservice method1 that returns a list of the functions(sproc) with their parameters and type.
- Then build an string with the function it needs to call and the parameters with their values encoded in some form.
- Then call  webservice method2 which calls the sproc and returns some value.

Is this correct?
0

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 5
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now