Execute multple stored procedures stored in a table

Question

Execute multple stored procedures stored in a table

kazim.raza

SSCrazy

Points: 2390
More actions
January 13, 2010 at 5:12 am

#218211

I am not sure if this is the right place for this question - but hopefully someone will be able to lead me in the right direction.
I have a table that holds multiple stored procedures and/or queries as per the user input. I want to write a stored procedure where I can execute them by passing to an EXEC or sp_executesql.
How can I achieve it by avoiding a cursor or loop? Or is it like I will have to use one of them?
Thank you.
Kazim Raza

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

well first let me throw out the standard security caveat: executing unverified, unsecure TSQL code that comes from an external source is a bad thing.

I'd love to hear the reasoning behind this requirement.

Anyway, since the code is in a table, and each row would need to be executed, you don't have a choice but dynamic SQL and some kind of a loop construct, whether it is a cursor or WHILE loop.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

kazim.raza SSCrazy Points: 2390 More actions · Answer 2

Thanks for the prompt response, and I was looking forward a request for reasoning behind the approach :-).

The target audience and users of my piece of the application would be technical people Business/System analysts etc. and we have to give them a way to execute a query of their own to short list or construct a data set and use it for further analysis,without having them to go and play with the actual data and database. Any ideas/suggestions for a better approach are always welcome and appreciated!

Thanks again for the reply.

Regards,

Kazim Raza

Lowell SSC Guru Points: 323518 More actions · Answer 3

I see; sort of like a self contained mini Query Analyzer/SSMS interface; I understand that, but why save the entry to be executed then?

i had done something along those lines, but it was limited to creating reports. a savvy person could copy/paste a SQL, and the SQL was used to select from the database.

In my case, there was an assumption that only queries that returned a dataset would be executed, creating excel like resultsets that could be manipulated with grouping functions that are part of the .NET grid suite I'm using; because of that, I ran the code thru some regular expressions to make sure it didn't contain any keywords like "INSERT,UPDATE,DBCC,INTO,etc" to make sure they were not going to affect the database.

Then any sql command executed wrapped in a transaction, so the command was executed, and then the transaction was rolled back, as a second layer to make sure no changes were made.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

That's what anaylisis services and cubes where built for.

Sharepoint might also be a solution if they need to share a lot of their work and input "changes" in the reports like targets and objectives..

Lowell SSC Guru Points: 323518 More actions · Answer 5

i'm just guessing at your interface, but do you have some UI that has a link per SQL statement and they click one link to get the results, vs running a bunch of statements one after the other?

If it is an application, why aren't you using a SQLCommand object to execute the text of the TSQL statment vs passing a statement (the same way) to sp_executeSQL? wouldn't that be easier?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

kazim.raza SSCrazy Points: 2390 More actions · Answer 6

Um; not exactly like query analyzer but more of a query builder, with no option of viewing the results, at least for the time being. And the reason why I am saving these in the database; its like a periodic process that will run those queries, mark some data as vaild/invalid and forward the data for further processing to other pieces of the application.

I am providing a very basic UI at the moment, it has a drop down listing all columns, another one for operators and a text box for specifying a value.

I could have probably done it by employing SSIS, perhaps in a much better way, but having SSIS would have been a major architectural decision that our lead architect didn't want to take risk with.

kazim.raza SSCrazy Points: 2390 More actions · Answer 7

It's a web application with the interface I described earlier.

kazim.raza SSCrazy Points: 2390 More actions · Answer 8

Um, I advanced on with it and now I am stuck figuring out how to handle stored procedures that have Parameters and values.

Here's the structure:

QueryDefinition:

QueryID BigInt PK Identity 1,1

QueryDefinition nVarchar(Max)

QueryType BigInt FK

This table is the master table that could hold multiple queries/SP defined by QueryType (1=SP, 2=Query).

SampleData_QueryDefinitionRel

QueryDefinitionRelID Bigint PK Identity 1,1

QueryID BigInt FK

SampleDataID BigInt FK

QueryExecOrder SmallInt

This table is the child and you can associate multiple queries to multiple Sample Data and their execution order in case multiple rows from QueryDefinition are associated with a single record from SampleData.

SampleData

SampleDataID BigInt PK identity 1,1

SampleDataRunTime DateTime

SampleData holds a few more columns which basically are activation checks and time slots when to run.

I am thinking on the lines of having two more columns in QueryDefinition table. One column would hold a comma separated list of variables (@ID, @FName) and the other their values (2, 'Kazim'). However; I am not really supportive of this approach and seek some guidance in this regard.

Regards,

Kazim Raza