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?
Answer
Execute SQL results count
+3Best answer by fhunth
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;
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;
+3I'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?
+17 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
+3That 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!!!!!
+17Do 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
+3I 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?
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.