Solved

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

Posted on 2014-12-03
12
187 Views
Last Modified: 2014-12-04
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
Comment
Question by:sweoff
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 2
  • +2
12 Comments
 
LVL 18

Expert Comment

by:UnifiedIS
ID: 40479083
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
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40479089
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
 

Author Comment

by:sweoff
ID: 40479122
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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 18

Assisted Solution

by:UnifiedIS
UnifiedIS earned 250 total points
ID: 40479150
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
 
LVL 18

Accepted Solution

by:
UnifiedIS earned 250 total points
ID: 40479178
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
 

Author Comment

by:sweoff
ID: 40479203
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
 

Author Comment

by:sweoff
ID: 40479209
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
 
LVL 10

Assisted Solution

by:Walter Padrón
Walter Padrón earned 125 total points
ID: 40479613
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
 

Author Comment

by:sweoff
ID: 40479656
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
 
LVL 50

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 125 total points
ID: 40480544
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
 

Author Closing Comment

by:sweoff
ID: 40480801
Thanks Guys
0
 
LVL 10

Expert Comment

by:Walter Padrón
ID: 40480973
@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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how the fundamental information of how to create a table.

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question