Store "Dynamic" results from a stored procedure into a table
-
Hi,
I have a procedure that returns different results(different number of columns and different datatypes) every time the procedure is executed. How can i capture the results of the stored procedure in a table?
Additional details :
We are trying to create an audit framework which will run queries as per a particular schedule. These results will be sent in an email. The queries that are run, is different every time because they go after different tables and yield different results.
The creation of the output table from the query provided can be achieved through OPENROWSET, but its not allowed due to security concerns. We wanted to see if this can be achieved in a "pure" SQL fashion.
Thanks in advance.
-
sandesh_krishnan (1/4/2016)
Hi,I have a procedure that returns different results(different number of columns and different datatypes) every time the procedure is executed. How can i capture the results of the stored procedure in a table?
Additional details :
We are trying to create an audit framework which will run queries as per a particular schedule. These results will be sent in an email. The queries that are run, is different every time because they go after different tables and yield different results.
The creation of the output table from the query provided can be achieved through OPENROWSET, but its not allowed due to security concerns. We wanted to see if this can be achieved in a "pure" SQL fashion.
Thanks in advance.
Do you have some idea of what the results table might look like? Are the results going to shoved into a single column with an xml datatype or something? You are going to have to do something like that because you can't just randomly store different columns and datatypes per row. That is NOT how relational data works. A bit more explanation of what you are trying to do and we can easily help you figure something out.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
Can you modify the procedure, since you cannot use openquery?
sounds like a one procedure to rule them all situation, not the best way to do things.
I would modify the proc to have a new default parameter @SendToGlobalTemp bit = 0
then modify the proc so that If the SendToGlobalTemp =1,the procedure drops and recreates a global temp table(ie ##MyResults) each time it runs, and it simply inserts data into a global temp table.
then the calling procedure can select * from ##MyResults.
if this is called concurrently, with other params, it could be wierd, but that's how i would do it.
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! -
Lowell (1/4/2016)
Can you modify the procedure, since you cannot use openquery?sounds like a one procedure to rule them all situation, not the best way to do things.
I would modify the proc to have a new default parameter @SendToGlobalTemp bit = 0
then modify the proc so that If the SendToGlobalTemp =1,the procedure drops and recreates a global temp table(ie ##MyResults) each time it runs, and it simply inserts data into a global temp table.
then the calling procedure can select * from ##MyResults.
if this is called concurrently, with other params, it could be wierd, but that's how i would do it.
Isn't it funny that some DBAs will take a chance and allow possibly non-concurrent code to run but won't let a stored procedure run OPENROWSET? You've just got to wonder sometimes.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Lowell (1/4/2016)
Can you modify the procedure, since you cannot use openquery?sounds like a one procedure to rule them all situation, not the best way to do things.
I would modify the proc to have a new default parameter @SendToGlobalTemp bit = 0
then modify the proc so that If the SendToGlobalTemp =1,the procedure drops and recreates a global temp table(ie ##MyResults) each time it runs, and it simply inserts data into a global temp table.
then the calling procedure can select * from ##MyResults.
if this is called concurrently, with other params, it could be wierd, but that's how i would do it.
I agree with the "one procedure to rule them all" assessment - both your hunch that this is such a situation, and your comment that this is not the best way to do things.
To the OP: that may be a nice design in other languages and frameworks, but it is not in SQL. The best way to solve this situation is to create multiple stored procedures that each always return the same dataset.
Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/ -
More details .....
I have a T-SQL query provided by users of our data, which goes after multiple tables and generates an output. There are multiple queries similar to this which will be used to go after different tables. The results of these scripts need to be captured in a table/placeholder and then sent in an email. These placeholders need not be permanent.
The queries will return different results each time and the queries run in 30 to 40 kinds. We cannot afford to have a separate table structure or procedure to accommodate all these results. That is the reason, we are looking for a more generic approach.
Hope this provides more insight.
-
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! -
sandesh_krishnan (1/5/2016)
More details .....I have a T-SQL query provided by users of our data, which goes after multiple tables and generates an output. There are multiple queries similar to this which will be used to go after different tables. The results of these scripts need to be captured in a table/placeholder and then sent in an email. These placeholders need not be permanent.
The queries will return different results each time and the queries run in 30 to 40 kinds. We cannot afford to have a separate table structure or procedure to accommodate all these results. That is the reason, we are looking for a more generic approach.
Hope this provides more insight.
The way I see it, you can either attempt to write a single reusable framework with lots of dynamic SQL and lots of conditional logic that will handle all possible results, but be unmainatainable, probably slow, and pose a security risk.
Or you can bite the bullet and just write those 30-40 stored procedures. Will probably involve a lot of copy/paste and be quite boring to do, but that's why you get paid to do it.
I think you can guess which option I recommend. 🙂
Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/ -
Hugo Kornelis (1/5/2016)
sandesh_krishnan (1/5/2016)
More details .....I have a T-SQL query provided by users of our data, which goes after multiple tables and generates an output. There are multiple queries similar to this which will be used to go after different tables. The results of these scripts need to be captured in a table/placeholder and then sent in an email. These placeholders need not be permanent.
The queries will return different results each time and the queries run in 30 to 40 kinds. We cannot afford to have a separate table structure or procedure to accommodate all these results. That is the reason, we are looking for a more generic approach.
Hope this provides more insight.
The way I see it, you can either attempt to write a single reusable framework with lots of dynamic SQL and lots of conditional logic that will handle all possible results, but be unmainatainable, probably slow, and pose a security risk.
Or you can bite the bullet and just write those 30-40 stored procedures. Will probably involve a lot of copy/paste and be quite boring to do, but that's why you get paid to do it.
I think you can guess which option I recommend. 🙂
I agree with Hugo. You have make a choice if you want it right or if you want it right now. You can't have both. 😀
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
There are three approaches that can be used to solve this problem.
1. Use OPENROWSET - Not all environments allow this approach. This is a security concern.
2. Use of .NET CLR which grabs the dynamic results from the procedure.
3. Use of OPEN XML - The output of the dynamic query is captured in XML and then parsed to get the results in a readable format.
Option 1 is ruled out in my case and option 3 looks pretty achievable.
Thanks for all the help!
-
Yes, you are right. I did not think about XML.
If you need to return data in unpredicatable and ever changing form, then XML is a good tool. But remember that XML is mainly good for returning free-form data to a client. Storing XML data in a database and trying to work on the elements in it will not give you much pleasure - it's hard and slow if the schema is known, and close to impossible if the schema is not known.
Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/ -
It's difficult for me to understand why anyone is even entertaining the idea of a universal query, never mind doing it with XML that still needs to be parsed.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
If it's just the thought of writing 30-40 SPs that gives you pause, you can always write a program that writes the code for you. I did this to create the 1400 CRUD SPs my app needed. Took a day to write the CRUD creator, as opposed to doing it by hand, which could have taken weeks (and been a bigger hit to my sanity than a one-on-one fight with Cthulhu... :w00t:)
-
roger.plowman (1/6/2016)
If it's just the thought of writing 30-40 SPs that gives you pause, you can always write a program that writes the code for you. I did this to create the 1400 CRUD SPs my app needed. Took a day to write the CRUD creator, as opposed to doing it by hand, which could have taken weeks (and been a bigger hit to my sanity than a one-on-one fight with Cthulhu... :w00t:)+1000 on that. Plus, it can be used for future CRUD.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply