July 25, 2018 at 2:15 am
Hi,
Just like @@ROWCOUNT stores the record count of the last executed Query,
Similarly, do we have any variable/functionality that stores/returns all the records and column information of the last executed SELECT query.
I do not want to write any code before the SELECT query (like " Insert INTO .. FROM ..." or "Create table as Select ...." )
After the SELECT query has executed , is there any way to get the entire structure and records of the Select query output through some inbuilt system
variable?
Rgds
-J
July 25, 2018 at 4:54 am
If you mean something along the lines of, within T-SQL:
SELECT * FROM LastSelectResultSet
Then the answer is no. However, if you want to see what's happening on your server, which queries with which parameters are being called, where, how and by who, yeah, there is something for that. It's called Extended Events. It'll show you exactly that information. If you're in SQL Server 2016 or better you can also use the Query Store to gather aggregate information about all the queries in a database.
That won't also return the data though. The only way to return the data from a query is to execute the query. SQL Server doesn't keep two copies of the data around, one copy that it returns to the requester and a second copy, just in case, in memory. Nor would you want it to. Depending on the memory pressure on your system, after a query, the pages that were necessary to satisfy that query are in memory, and if another query comes along that needs those same pages, they will be read from memory instead of from disk, but there still isn't a stored result set that you can re-access after the fact.
What purpose would this functionality serve if you had it?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 25, 2018 at 7:19 am
Thanks Grant for the details provided.
There is an existing Stored Procedure in SS which when run by an application from a remote location populates the local table within the application.
This SP has many Select statements out of which only one gets executed (based on certain criteria) and the result of the select query(which has joins on multiple tables) gets populated into the applicable local table remotely.
For data integrity, I wanted to add code in the SP and add a table level checksum (i.e., on the results generated by the query.. which is not stored in any temp or permanent tables in SQL Server but are directly loaded into remote APP local tables...) . If such a result-set existed(enough for a short period) then my task would have been easier and safe.
1) the safest and the fastest was to not touch existing code and get result-set through some system variable(if it existed..) and hence posted this to check if more experienced guys like you know of any such thing...
2) there would be other approaches that I could apply but now I will have to be more cautious and do more testing to ensure I don't impact existing setup.
Anyway thanks for the reply.
Cheers!
July 25, 2018 at 7:28 am
Have you looked into the OUTPUT clause. It sounds like that might serve your purposes. It's available in INSERT, UPDATE, DELETE, and MERGE statements and allows you to generate a second result set that you can query or store.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
July 25, 2018 at 7:56 am
Yeah, I agree with Drew. Rather than try to capture the SELECT statement, just capture the data from the data modification through the OUTPUT clause.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 25, 2018 at 10:14 am
Thanks Drew for your reply.
But I didn't get how OUTPUT clause would serve my purpose as you mentioned that its used only with INSERT, UPDATE, DELETE & MERGE. And I have only SELECT statements ( in my Stored Procedure ) whose output I am interested in.
-MJ
July 25, 2018 at 10:23 am
mahnj18 - Wednesday, July 25, 2018 10:14 AMThanks Drew for your reply.
But I didn't get how OUTPUT clause would serve my purpose as you mentioned that its used only with INSERT, UPDATE, DELETE & MERGE. And I have only SELECT statements ( in my Stored Procedure ) whose output I am interested in.-MJ
You said that it "populates' the local table. I assumed you were using INSERT to do that population. If the app is just reading one of the result sets, then you can store the data first and use the OUTPUT clause to generate the result set that is then consumed by your app.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
July 25, 2018 at 10:42 am
No, Application will have its own database and tables which are not accessible from SQL Server. Application triggers SQL procedure and through it pulls data from SQLServer into their local DB. Moreover main purpose is to valid data between SQL Server & App DB. So would require data before its loaded at some remote location(in some APP tables).
Thanks!
-MJ
July 25, 2018 at 11:44 am
I'd monitor it using Extended Events (or if you're on 2008, Trace Events). You can capture the query. You won't see the data, but you can see the query.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply