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
175 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:ScottPletcher
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
 
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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 

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 45

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
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.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

705 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now