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?
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.
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.
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?
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

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:
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?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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.
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,
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
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.
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.
AS SELECT id, name FROM TableB
INNER JOIN TableB ON TableA.id=TableB.id
WHERE TableA.id=@ID

Open in new window

Then you'll call each SP with the correspondent number. Example for the 3: EXEC MySPName(999);3
sweoffAuthor Commented:
Thanks Guys
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?
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.