cancel
Showing results for 
Search instead for 
Did you mean: 
Workflow Hero

Execute SQL results count

Jump to solution

I know it help it says update your query to retrieve the next 200 records, how do you set a loop to reach the end of record count?

Labels: (1)
Tags (3)
Reply
6 Replies
Workflow Hero

Re: Execute SQL results count

Jump to solution

ROW_NUMBER() seems to work:

Paging with ROW_NUMBER()

DECLARE @PageNum AS INT;
DECLARE @PageSize AS INT;
SET @PageNum = 2;
SET @PageSize = 10;

WITH OrdersRN AS
(
SELECT ROW_NUMBER() OVER(ORDER BY OrderDate, OrderID) AS RowNum
,OrderID
,OrderDate
,CustomerID
,EmployeeID
FROM dbo.Orders
)

SELECT *
FROM OrdersRN
WHERE RowNum BETWEEN (@PageNum - 1) * @PageSize + 1
AND @PageNum * @PageSize
ORDER BY OrderDate,OrderID;

Reply
Workflow Hero

Re: Execute SQL results count

Jump to solution

I'm sorry I should rephrase my question.  I need to retrieve around 6,000 records that are unique identifiers for our customers.  How can I loop through to get all 6,000 records? 

0 Kudos
Reply
Workflow Hero

Re: Execute SQL results count

Jump to solution

‌ so if I understood you correctly, what you need is not to loop through the records using Execute SQL action, but you want to get them all at once and the iterate through the collection? If so, then just save output of the Execute SQL into a collection and then do "For Each" to iterate through it.

Regards,

Tomasz

Regards, Tomasz Poszytek
Reply
Workflow Hero

Re: Execute SQL results count

Jump to solution

That is correct but how can I retrieve all records if the max results a execute sql function will return is 200?  That's where I am getting stuck. Thanks for your response!!!!!

0 Kudos
Reply
Workflow Hero

Re: Execute SQL results count

Jump to solution

Do it in a loop then, using suggestions from Fernando and the "Row_Number" - in each loop get next 200 rows, then evaluate if query returned anything, in case yes - process them and then increase the "PageNum" variable. PageSize in that case is 200.

Regards,

Tomasz

Regards, Tomasz Poszytek
Reply
Workflow Hero

Re: Execute SQL results count

Jump to solution

I get the logic and have it working in SMSS but haw can I pass the increased variable to a variable inside my execute SQL statement?

Reply