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.

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.

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.