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
184 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
  • 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
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 48

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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
query question 12 32
t-sql need help on t-sql 10 25
Need return values from a stored procedure 8 18
Need help in debugging a UDF results 7 14
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how the fundamental information of how to create a table.

790 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