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.
Best Answer
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:
The central query takes all the records from
#QueryList
, and builds anINSERT
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 ownSELECT
statement, and all the statements are combined into a single result set withUNION ALL
. I have taken text files with thousands of rows, converted each one into a similarSELECT ... 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 theUNION ALL SELECT ...
from each row's values into a single string, theSTUFF
replaces the firstUNION ALL
with the actualINSERT INTO #results
. See this article for more information on aggregating string data across multiple rows (or, move to SQL 2017 and useSTRING_AGG()
).Something else that you may not have seen before is
FROM (<subquery>) AS qr ([Result])
. Just as theAS qr
makesqr
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:
And, the contents of
#results
after the query is executed:Notes:
#QueryList
that used a CTE), this will fail.;
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).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 thatId
andName
taken together, are unique in #QueryList
, then:would let you grab 1000
#QueryList
rows at a time, and only grab ones where theId
andName
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
isNULL
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.