September 18, 2009 at 12:23 pm
I've got a set of queries that I need to capture the results from without actually modifiying the query. For example:
DROP TABLE #test
CREATE TABLE #test (
cola NVARCHAR(10)
)
INSERT INTO #test values ('a'),('b'),('c')
-- This is the starting SQL - we want to capture its output - it can be any datatype,
-- but only 1 row and 1 column. And its not always going to be a count - this is just
-- an example
DECLARE @SQL NVARCHAR(MAX) = 'SELECT COUNT(*) FROM #test'
DECLARE @Results NVARCHAR(MAX)
With this starting place I'm trying to populate @Results with the 1 column 1 row result that @sql would give when run.
Methods that I have tried was encapsulating @sql in a sub-query, trying to execute that using normal execute, sp_executesql, etc. Nothing I've tried seems to get the results from that query into @Results.
I know the easy way would be modifying the query to:
SELECT @Results = COUNT(*) FROM #test
But I'm trying to get around putting stipulations like that on all of these queries.
Has anyone tried to solve a problem like this? I've done a bunch of google searches and keep running into a brick wall. Any pointers or even a "can't do that" would help a ton.
Thanks!
September 18, 2009 at 1:39 pm
Just add these three lines onto the end of your script:
DECLARE @AdjSQL NVARCHAR(MAX) = 'SELECT @ret = ' + SUBSTRING(@SQL, 7, LEN(@SQL));
EXEC sys.sp_executeSql @AdjSQL, N'@ret AS INTEGER OUTPUT', @Results OUTPUT;
Select @Results;
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 18, 2009 at 1:41 pm
Note, this particular approach only works if your @sql variable starts with 'SELECT '.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 18, 2009 at 1:54 pm
This is perfect and is exactly what I needed. I checked all the existing scripts in the DB and they all start with SELECT so this may do the trick. Thanks! It didn't even occur to me to rewrite the query.
September 19, 2009 at 3:10 pm
Glad we could help!
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply