Retrieve value of select query in a table column

Question

I need an alternative to cursor/while loop for below query:

We have a table as given in below screen shot containing the following columns: ID, Module, Query. The table contains nearly 100,000 records. Currently we are using cursor and a while loop to insert the output of the select query to a temporary table which we are joining with the actual table to update based on ID.

enter image description here

I need a help in writing a query that takes the Query column as input and gives the result as value like a function.

I tried creating a function which executes dynamic SQL but it failed because EXEC and sp_executesql aren't allowed within a function. Attempts to use a stored procedure also didn't work because we can pass only one query as a parameter from this table to get output as value.

Each query returns a single value (one column, one row). Different queries may return values with different datatypes; all should be converted to nvarchar.

Any solution would be greatly appreciated.

• paparazzo: .NET application
• Joe Obbish: Can you write an example query with a placeholder for the unknown part? How do you specify which queries need to be passed in? Can you also explain why you have this requirement?
• theo: assuming all the query values return the same output, can you just append "insert into [Result] ([Field1])" to the beginning and then use the stuff function to append some or all of your [Query] values with "union all" between them? -- then execute that as dynamic sql either all together or in batches (not sure if there is a limit on how many selects you can union together).
• info.sqldba: @theo all the query values return the same output: No it will be different. can you please provide a small example that i can use with union as you said.
• ypercubeᵀᴹ: Step 1: INSERT INTO QueryList (Name, Query) VALUES ('BadBobbyTables', 'DROP TABLE QueryList;') ; Step 2: Wait for query list to self-detonate.

Answer 1

I've tested the following (in SQL Server 2014, but it should work at least as far back at 2008), and I believe it does what you want:

CREATE TABLE QueryList (Id int, Name nvarchar(128), Query nvarchar(4000));
INSERT INTO QueryList
VALUES (1, 'Date', 'SELECT GETDATE()')
 ,(2, 'String', 'SELECT ''What''''s this? Just a varchar string...'' as StrVal')
 ,(3, 'Server', 'SELECT @@SERVERNAME')
;
CREATE TABLE results (Id int, Name nvarchar(128), Result nvarchar(4000));
-----
DECLARE @stmt nvarchar(max);
SELECT @stmt = stuff( (SELECT N'UNION ALL SELECT TOP (1) ' + CAST(Id as nvarchar(10))
 +N' as Id, ''' + Name + N''', CAST(qr.[Result] as nvarchar(4000)) as [Result] '
 +N'FROM (' + Query + N') AS qr ([Result])
'
 FROM QueryList
 FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)')
 ,1,9,'INSERT INTO results') + ';'
;
SELECT @stmt; -- FOR TESTING - displays the resulting query
EXECUTE sp_executesql @stmt;
SELECT * FROM results;

The central query takes all the records from #QueryList, and builds an INSERT statement to place the id, name, and the query result from each row into the #results temp table. Each row's values are in their own SELECT statement, and all the statements are combined into a single result set with UNION ALL. I have taken text files with thousands of rows, converted each one into a similar SELECT ... UNION ALL, and used that to successfully insert thousands of records into SQL Server tables; I don't expect the number of statements being unioned together to be an issue (but I can't guarantee there isn't a limit at some point).

If you haven't seen it before, this uses FOR XML PATH to concatenate the UNION ALL SELECT ... from each row's values into a single string, the STUFF replaces the first UNION ALL with the actual INSERT INTO #results. See this article for more information on aggregating string data across multiple rows (or, move to SQL 2017 and use STRING_AGG()).

Something else that you may not have seen before is FROM (<subquery>) AS qr ([Result]). Just as the AS qr makes qr and alias for the subquery, ([Result]) provides a list of aliases for the columns (in our case, column) in the subquery.

Here's the query that's built from the above data:

INSERT INTO #results SELECT TOP (1) 1 as Id, 'Date', CAST(qr.[Result] as nvarchar(4000)) as [Result] FROM (SELECT GETDATE()) AS qr ([Result])
UNION ALL SELECT TOP (1) 2 as Id, 'String', CAST(qr.[Result] as nvarchar(4000)) as [Result] FROM (SELECT 'This is just a varchar string' as StrVal) AS qr ([Result])
UNION ALL SELECT TOP (1) 3 as Id, 'Server', CAST(qr.[Result] as nvarchar(4000)) as [Result] FROM (SELECT @@SERVERNAME) AS qr ([Result])
;

And, the contents of #results after the query is executed:

Id Name Result
---- -------- --------------------------------------
1 Date Jul 14 2017 12:19PM
2 String What's this? Just a varchar string...
3 Server MYSERVERNAME\INSTANCE

Notes:

• If your query cannot trivially be used as a subquery (for instance, if you had a query in #QueryList that used a CTE), this will fail.
• If you included a closing ; at the end of a query in #QueryList, it will fail (special case of not being trivially used as a subquery; I almost included this in my #QueryList queries, by force of habit).
• To be on the safe side, this allows for the possibility that some query may return multiple rows, and simply selects the first row as the "right" value, at random.

• If you do run into an issue with the number of statements being combined via UNION ALL, you can break this down into smaller chunks. Assuming that Id and Name taken together, are unique in #QueryList, then:

  SELECT @stmt = stuff( (SELECT TOP (10000) N'UNION ALL SELECT TOP (1) ' + CAST(ql.Id as nvarchar(10))
   +N' as Id, ''' +ql. Name + N''', CAST(qr.[Result] as nvarchar(4000)) as [Result] '
   +N'FROM (' + ql.Query + N') AS qr ([Result])
  '
   FROM #QueryList ql
   LEFT JOIN #results r ON (ql.Id = r.Id AND ql.Name = r.Name)
   WHERE r.Id IS NULL
   FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)')
   ,1,9,'INSERT INTO results') + ';'
  ;
  

  would let you grab 1000 #QueryList rows at a time, and only grab ones where the Id and Name don't already exist in #results. You'd have to put this in a loop, and keep going until there were no more rows left in #QueryList. @stmt is NULL if there are no more rows to process, so you could use that to break out of the loop.

  Again, I don't expect this to be an issue. And, even if it is, while it takes you back to a WHILE loop, you're dealing with 10,000 (or more) rows at a time, not one. It should be faster than your current cursor implementation.

  And - if you find that there are some queries that cause problems, this could be useful for testing; go through subsets of data until you find a set that doesn't work, then check those queries to see if there's an issue with one.

• info.sqldba: I would really appreciate and very thankful for giving me solution with an example , i made it to work, but i am using a loop to finish in batches of the queries, because as i already said i have 53000 records like this if i build dynamically i am able to get 220 records single time to get inserted into #result table, and one more problem i am not able to handle is, As we are using nvarchar(4000) for @stmt so it can build it can upto allowed characters, at end of built query half finished or half broken i can say ex: **select TOP(1) 221 as Id, 'ABCD', CAST(qr.[Resu **
• RDFozz: The example I provided uses nvarchar(max). Is there a reason you can't use that? If you must use nvarchar(4000), all I can recommend is that you check the maximum length of a query, and keep the number of rows you process at a time small enough that you won't go over 4000 characters total, using the TOP (X) version of the query.