{"id":4223,"date":"2024-10-03T12:21:06","date_gmt":"2024-10-03T04:21:06","guid":{"rendered":"https:\/\/five.co\/?p=4223"},"modified":"2026-02-25T15:24:42","modified_gmt":"2026-02-25T07:24:42","slug":"sql-loop-through-table","status":"publish","type":"post","link":"https:\/\/preview-php85.dzine.org\/five\/blog\/sql-loop-through-table\/","title":{"rendered":"SQL Loop Through Table: Practical Guide"},"content":{"rendered":"\n<h2 class=\"wp-block-heading\">SQL Loop Through Table Fully Explained<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Looping through tables in SQL might seem counterintuitive at first. <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">After all, SQL is designed for set-based operations, not procedural programming. However, there are scenarios where row-by-row processing is necessary or even more efficient. <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Let&#8217;s explore various techniques to loop through tables in SQL, complete with practical examples and real-world use cases.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n<div class=\"root-eb-toc-tlw9a wp-block-essential-blocks-table-of-contents\"><div class=\"eb-parent-wrapper eb-parent-eb-toc-tlw9a \"><div class=\"eb-toc-container eb-toc-tlw9a  eb-toc-is-not-sticky eb-toc-not-collapsible eb-toc-initially-not-collapsed eb-toc-scrollToTop style-1 list-style-none\" data-scroll-top=\"false\" data-scroll-top-icon=\"fas fa-angle-up\" data-collapsible=\"false\" data-sticky-hide-mobile=\"false\" data-sticky=\"false\" data-scroll-target=\"scroll_to_toc\" data-copy-link=\"false\" data-editor-type=\"\" data-hide-desktop=\"false\" data-hide-tab=\"false\" data-hide-mobile=\"false\" data-itemcollapsed=\"false\" data-highlight-scroll=\"false\"><div class=\"eb-toc-header\"><h2 class=\"eb-toc-title\">Table of Contents<\/h2><\/div><div class=\"eb-toc-wrapper \" data-headers=\"[{&quot;level&quot;:2,&quot;content&quot;:&quot;SQL Loop Through Table Fully Explained&quot;,&quot;text&quot;:&quot;SQL Loop Through Table Fully Explained&quot;,&quot;link&quot;:&quot;sql-loop-through-table-fully-explained&quot;},{&quot;level&quot;:2,&quot;content&quot;:&quot;Using Cursors: The Traditional Approach&quot;,&quot;text&quot;:&quot;Using Cursors: The Traditional Approach&quot;,&quot;link&quot;:&quot;using-cursors-the-traditional-approach&quot;},{&quot;level&quot;:2,&quot;content&quot;:&quot;WHILE Loop with ROW_NUMBER()&quot;,&quot;text&quot;:&quot;WHILE Loop with ROW_NUMBER()&quot;,&quot;link&quot;:&quot;while-loop-with-row_number&quot;},{&quot;level&quot;:2,&quot;content&quot;:&quot;Using a Tally Table for Looping&quot;,&quot;text&quot;:&quot;Using a Tally Table for Looping&quot;,&quot;link&quot;:&quot;using-a-tally-table-for-looping&quot;},{&quot;level&quot;:2,&quot;content&quot;:&quot;Real-World Scenario: Updating Customer Statuses&quot;,&quot;text&quot;:&quot;Real-World Scenario: Updating Customer Statuses&quot;,&quot;link&quot;:&quot;real-world-scenario-updating-customer-statuses&quot;},{&quot;level&quot;:2,&quot;content&quot;:&quot;Looping for Data Migration&quot;,&quot;text&quot;:&quot;Looping for Data Migration&quot;,&quot;link&quot;:&quot;looping-for-data-migration&quot;},{&quot;level&quot;:2,&quot;content&quot;:&quot;Using Temp Tables for Efficient Looping&quot;,&quot;text&quot;:&quot;Using Temp Tables for Efficient Looping&quot;,&quot;link&quot;:&quot;using-temp-tables-for-efficient-looping&quot;},{&quot;level&quot;:2,&quot;content&quot;:&quot;Set-Based Alternatives to Looping&quot;,&quot;text&quot;:&quot;Set-Based Alternatives to Looping&quot;,&quot;link&quot;:&quot;set-based-alternatives-to-looping&quot;},{&quot;level&quot;:2,&quot;content&quot;:&quot;Handling Errors in Loops&quot;,&quot;text&quot;:&quot;Handling Errors in Loops&quot;,&quot;link&quot;:&quot;handling-errors-in-loops&quot;},{&quot;level&quot;:2,&quot;content&quot;:&quot;Conclusion: Using SQL Table Looping&quot;,&quot;text&quot;:&quot;Conclusion: Using SQL Table Looping&quot;,&quot;link&quot;:&quot;conclusion-using-sql-table-looping&quot;},{&quot;level&quot;:2,&quot;content&quot;:&quot;FAQ&quot;,&quot;text&quot;:&quot;FAQ&quot;,&quot;link&quot;:&quot;faq&quot;},{&quot;level&quot;:3,&quot;content&quot;:&quot;When should you loop through a table in SQL instead of using a set-based query?&quot;,&quot;text&quot;:&quot;When should you loop through a table in SQL instead of using a set-based query?&quot;,&quot;link&quot;:&quot;when-should-you-loop-through-a-table-in-sql-instead-of-using-a-set-based-query&quot;},{&quot;level&quot;:3,&quot;content&quot;:&quot;What is the most common way to loop through rows in SQL?&quot;,&quot;text&quot;:&quot;What is the most common way to loop through rows in SQL?&quot;,&quot;link&quot;:&quot;what-is-the-most-common-way-to-loop-through-rows-in-sql&quot;},{&quot;level&quot;:3,&quot;content&quot;:&quot;Are there more efficient alternatives to cursors for looping?&quot;,&quot;text&quot;:&quot;Are there more efficient alternatives to cursors for looping?&quot;,&quot;link&quot;:&quot;are-there-more-efficient-alternatives-to-cursors-for-looping&quot;},{&quot;level&quot;:3,&quot;content&quot;:&quot;What are some real-world use cases for looping through tables?&quot;,&quot;text&quot;:&quot;What are some real-world use cases for looping through tables?&quot;,&quot;link&quot;:&quot;what-are-some-real-world-use-cases-for-looping-through-tables&quot;},{&quot;level&quot;:3,&quot;content&quot;:&quot;Why should you consider set-based solutions before using loops?&quot;,&quot;text&quot;:&quot;Why should you consider set-based solutions before using loops?&quot;,&quot;link&quot;:&quot;why-should-you-consider-set-based-solutions-before-using-loops&quot;}]\" data-visible=\"[true,true,true,true,true,true]\" data-delete-headers=\"[{&quot;label&quot;:&quot;SQL Loop Through Table Fully Explained&quot;,&quot;value&quot;:&quot;sql-loop-through-table-fully-explained&quot;,&quot;isDelete&quot;:true},{&quot;label&quot;:&quot;Using Cursors: The Traditional Approach&quot;,&quot;value&quot;:&quot;using-cursors-the-traditional-approach&quot;,&quot;isDelete&quot;:false},{&quot;label&quot;:&quot;WHILE Loop with ROW_NUMBER()&quot;,&quot;value&quot;:&quot;while-loop-with-row_number&quot;,&quot;isDelete&quot;:false},{&quot;label&quot;:&quot;Using a Tally Table for Looping&quot;,&quot;value&quot;:&quot;using-a-tally-table-for-looping&quot;,&quot;isDelete&quot;:false},{&quot;label&quot;:&quot;Real-World Scenario: Updating Customer Statuses&quot;,&quot;value&quot;:&quot;real-world-scenario-updating-customer-statuses&quot;,&quot;isDelete&quot;:false},{&quot;label&quot;:&quot;Looping for Data Migration&quot;,&quot;value&quot;:&quot;looping-for-data-migration&quot;,&quot;isDelete&quot;:false},{&quot;label&quot;:&quot;Using Temp Tables for Efficient Looping&quot;,&quot;value&quot;:&quot;using-temp-tables-for-efficient-looping&quot;,&quot;isDelete&quot;:false},{&quot;label&quot;:&quot;Set-Based Alternatives to Looping&quot;,&quot;value&quot;:&quot;set-based-alternatives-to-looping&quot;,&quot;isDelete&quot;:false},{&quot;label&quot;:&quot;Handling Errors in Loops&quot;,&quot;value&quot;:&quot;handling-errors-in-loops&quot;,&quot;isDelete&quot;:false},{&quot;label&quot;:&quot;Conclusion: Using SQL Table Looping&quot;,&quot;value&quot;:&quot;conclusion-using-sql-table-looping&quot;,&quot;isDelete&quot;:false},{&quot;label&quot;:&quot;FAQ&quot;,&quot;value&quot;:&quot;faq&quot;,&quot;isDelete&quot;:false},{&quot;label&quot;:&quot;When should you loop through a table in SQL instead of using a set-based query?&quot;,&quot;value&quot;:&quot;when-should-you-loop-through-a-table-in-sql-instead-of-using-a-set-based-query&quot;,&quot;isDelete&quot;:false},{&quot;label&quot;:&quot;What is the most common way to loop through rows in SQL?&quot;,&quot;value&quot;:&quot;what-is-the-most-common-way-to-loop-through-rows-in-sql&quot;,&quot;isDelete&quot;:false},{&quot;label&quot;:&quot;Are there more efficient alternatives to cursors for looping?&quot;,&quot;value&quot;:&quot;are-there-more-efficient-alternatives-to-cursors-for-looping&quot;,&quot;isDelete&quot;:false},{&quot;label&quot;:&quot;What are some real-world use cases for looping through tables?&quot;,&quot;value&quot;:&quot;what-are-some-real-world-use-cases-for-looping-through-tables&quot;,&quot;isDelete&quot;:false},{&quot;label&quot;:&quot;Why should you consider set-based solutions before using loops?&quot;,&quot;value&quot;:&quot;why-should-you-consider-set-based-solutions-before-using-loops&quot;,&quot;isDelete&quot;:false}]\" data-smooth=\"true\" data-top-offset=\"\"><div class=\"eb-toc__list-wrap\"><ul class=\"eb-toc__list\"><li><a href=\"#using-cursors-the-traditional-approach\">Using Cursors: The Traditional Approach<\/a><li><a href=\"#while-loop-with-row_number\">WHILE Loop with ROW_NUMBER()<\/a><li><a href=\"#using-a-tally-table-for-looping\">Using a Tally Table for Looping<\/a><li><a href=\"#real-world-scenario-updating-customer-statuses\">Real-World Scenario: Updating Customer Statuses<\/a><li><a href=\"#looping-for-data-migration\">Looping for Data Migration<\/a><li><a href=\"#using-temp-tables-for-efficient-looping\">Using Temp Tables for Efficient Looping<\/a><li><a href=\"#set-based-alternatives-to-looping\">Set-Based Alternatives to Looping<\/a><li><a href=\"#handling-errors-in-loops\">Handling Errors in Loops<\/a><li><a href=\"#conclusion-using-sql-table-looping\">Conclusion: Using SQL Table Looping<\/a><li><a href=\"#faq\">FAQ<\/a><ul class=\"eb-toc__list\"><li><a href=\"#when-should-you-loop-through-a-table-in-sql-instead-of-using-a-set-based-query\">When should you loop through a table in SQL instead of using a set-based query?<\/a><li><a href=\"#what-is-the-most-common-way-to-loop-through-rows-in-sql\">What is the most common way to loop through rows in SQL?<\/a><li><a href=\"#are-there-more-efficient-alternatives-to-cursors-for-looping\">Are there more efficient alternatives to cursors for looping?<\/a><li><a href=\"#what-are-some-real-world-use-cases-for-looping-through-tables\">What are some real-world use cases for looping through tables?<\/a><li><a href=\"#why-should-you-consider-set-based-solutions-before-using-loops\">Why should you consider set-based solutions before using loops?<\/a><\/li><\/ul><\/ul><\/div><\/div><\/div><\/div><\/div>\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Using Cursors: The Traditional Approach<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Cursors are the most straightforward way to loop through a table in SQL. They allow you to process one row at a time. Here&#8217;s a basic example:<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#abb2bf;--cbp-line-number-width:calc(2 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#282c34\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" data-code=\"DECLARE @id INT, @name VARCHAR(50)\nDECLARE cur CURSOR FOR SELECT id, name FROM employees\nOPEN cur\nFETCH NEXT FROM cur INTO @id, @name\nWHILE @@FETCH_STATUS = 0\nBEGIN\n    -- Process each row\n    PRINT 'ID: ' + CAST(@id AS VARCHAR) + ', Name: ' + @name\n    FETCH NEXT FROM cur INTO @id, @name\nEND\nCLOSE cur\nDEALLOCATE cur\" style=\"color:#abb2bf;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki one-dark-pro\" style=\"background-color: #282c34\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #C678DD\">DECLARE<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #E06C75\">@id<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">INT<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #E06C75\">@name<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">VARCHAR<\/span><span style=\"color: #ABB2BF\">(<\/span><span style=\"color: #D19A66\">50<\/span><span style=\"color: #ABB2BF\">)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">DECLARE<\/span><span style=\"color: #ABB2BF\"> cur <\/span><span style=\"color: #C678DD\">CURSOR<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">FOR<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> id, <\/span><span style=\"color: #C678DD\">name<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> employees<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">OPEN<\/span><span style=\"color: #ABB2BF\"> cur<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">FETCH<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">NEXT<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> cur <\/span><span style=\"color: #C678DD\">INTO<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #E06C75\">@id<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #E06C75\">@name<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">WHILE<\/span><span style=\"color: #ABB2BF\"> @@FETCH_STATUS <\/span><span style=\"color: #56B6C2\">=<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">0<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">BEGIN<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #7F848E; font-style: italic\">-- Process each row<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">PRINT<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #98C379\">&#39;ID: &#39;<\/span><span style=\"color: #ABB2BF\"> + <\/span><span style=\"color: #56B6C2\">CAST<\/span><span style=\"color: #ABB2BF\">(<\/span><span style=\"color: #E06C75\">@id<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">VARCHAR<\/span><span style=\"color: #ABB2BF\">) + <\/span><span style=\"color: #98C379\">&#39;, Name: &#39;<\/span><span style=\"color: #ABB2BF\"> + <\/span><span style=\"color: #E06C75\">@name<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">FETCH<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">NEXT<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> cur <\/span><span style=\"color: #C678DD\">INTO<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #E06C75\">@id<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #E06C75\">@name<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">END<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">CLOSE<\/span><span style=\"color: #ABB2BF\"> cur<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">DEALLOCATE<\/span><span style=\"color: #ABB2BF\"> cur<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<div style=\"height:19px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">This code declares a cursor over the <code>employees<\/code> table, fetches each row, and prints the ID and name. While simple, cursors can be slow for large datasets and are often frowned upon due to performance concerns.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">WHILE Loop with ROW_NUMBER()<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">A more efficient alternative uses a WHILE loop with ROW_NUMBER():<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#abb2bf;--cbp-line-number-width:calc(2 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#282c34\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" data-code=\"DECLARE @id INT, @name VARCHAR(50), @rownum INT = 1\nWHILE EXISTS (SELECT 1 FROM (SELECT ROW_NUMBER() OVER (ORDER BY id) AS rownum FROM employees) t WHERE t.rownum = @rownum)\nBEGIN\n    SELECT @id = id, @name = name\n    FROM (SELECT ROW_NUMBER() OVER (ORDER BY id) AS rownum, id, name FROM employees) t\n    WHERE t.rownum = @rownum\n\n    -- Process each row\n    PRINT 'ID: ' + CAST(@id AS VARCHAR) + ', Name: ' + @name\n\n    SET @rownum = @rownum + 1\nEND\" style=\"color:#abb2bf;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki one-dark-pro\" style=\"background-color: #282c34\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #C678DD\">DECLARE<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #E06C75\">@id<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">INT<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #E06C75\">@name<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">VARCHAR<\/span><span style=\"color: #ABB2BF\">(<\/span><span style=\"color: #D19A66\">50<\/span><span style=\"color: #ABB2BF\">), <\/span><span style=\"color: #E06C75\">@rownum<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">INT<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">=<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">1<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">WHILE<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">EXISTS<\/span><span style=\"color: #ABB2BF\"> (<\/span><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">1<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> (<\/span><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">ROW_NUMBER<\/span><span style=\"color: #ABB2BF\">() <\/span><span style=\"color: #C678DD\">OVER<\/span><span style=\"color: #ABB2BF\"> (<\/span><span style=\"color: #C678DD\">ORDER BY<\/span><span style=\"color: #ABB2BF\"> id) <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> rownum <\/span><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> employees) t <\/span><span style=\"color: #C678DD\">WHERE<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">t<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">rownum<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">=<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #E06C75\">@rownum<\/span><span style=\"color: #ABB2BF\">)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">BEGIN<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #E06C75\">@id<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">=<\/span><span style=\"color: #ABB2BF\"> id, <\/span><span style=\"color: #E06C75\">@name<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">=<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">name<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> (<\/span><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">ROW_NUMBER<\/span><span style=\"color: #ABB2BF\">() <\/span><span style=\"color: #C678DD\">OVER<\/span><span style=\"color: #ABB2BF\"> (<\/span><span style=\"color: #C678DD\">ORDER BY<\/span><span style=\"color: #ABB2BF\"> id) <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> rownum, id, <\/span><span style=\"color: #C678DD\">name<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> employees) t<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">WHERE<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">t<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">rownum<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">=<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #E06C75\">@rownum<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #7F848E; font-style: italic\">-- Process each row<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">PRINT<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #98C379\">&#39;ID: &#39;<\/span><span style=\"color: #ABB2BF\"> + <\/span><span style=\"color: #56B6C2\">CAST<\/span><span style=\"color: #ABB2BF\">(<\/span><span style=\"color: #E06C75\">@id<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">VARCHAR<\/span><span style=\"color: #ABB2BF\">) + <\/span><span style=\"color: #98C379\">&#39;, Name: &#39;<\/span><span style=\"color: #ABB2BF\"> + <\/span><span style=\"color: #E06C75\">@name<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">SET<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #E06C75\">@rownum<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">=<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #E06C75\">@rownum<\/span><span style=\"color: #ABB2BF\"> + <\/span><span style=\"color: #D19A66\">1<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">END<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<div style=\"height:19px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">This method avoids the overhead of cursor management and can be more performant, especially for larger tables.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Using a Tally Table for Looping<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Tally tables can be incredibly useful for looping. Here&#8217;s how you might use one:<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#abb2bf;--cbp-line-number-width:calc(2 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#282c34\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" data-code=\"-- Create a tally table\nWITH \n    E1(N) AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) AS E1(N)),\n    E2(N) AS (SELECT 1 FROM E1 a, E1 b), -- 10 * 10 = 100 rows\n    E4(N) AS (SELECT 1 FROM E2 a, E2 b), -- 100 * 100 = 10,000 rows\n    Tally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4)\n\nSELECT e.id, e.name\nFROM employees e\nINNER JOIN Tally t ON t.N <= (SELECT COUNT(*) FROM employees)\nORDER BY e.id\" style=\"color:#abb2bf;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki one-dark-pro\" style=\"background-color: #282c34\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #7F848E; font-style: italic\">-- Create a tally table<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">WITH<\/span><span style=\"color: #ABB2BF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    E1(N) <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> (<\/span><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">1<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> (<\/span><span style=\"color: #C678DD\">VALUES<\/span><span style=\"color: #ABB2BF\"> (<\/span><span style=\"color: #D19A66\">1<\/span><span style=\"color: #ABB2BF\">),(<\/span><span style=\"color: #D19A66\">1<\/span><span style=\"color: #ABB2BF\">),(<\/span><span style=\"color: #D19A66\">1<\/span><span style=\"color: #ABB2BF\">),(<\/span><span style=\"color: #D19A66\">1<\/span><span style=\"color: #ABB2BF\">),(<\/span><span style=\"color: #D19A66\">1<\/span><span style=\"color: #ABB2BF\">),(<\/span><span style=\"color: #D19A66\">1<\/span><span style=\"color: #ABB2BF\">),(<\/span><span style=\"color: #D19A66\">1<\/span><span style=\"color: #ABB2BF\">),(<\/span><span style=\"color: #D19A66\">1<\/span><span style=\"color: #ABB2BF\">),(<\/span><span style=\"color: #D19A66\">1<\/span><span style=\"color: #ABB2BF\">),(<\/span><span style=\"color: #D19A66\">1<\/span><span style=\"color: #ABB2BF\">)) <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> E1(N)),<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    E2(N) <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> (<\/span><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">1<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> E1 a, E1 b), <\/span><span style=\"color: #7F848E; font-style: italic\">-- 10 * 10 = 100 rows<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    E4(N) <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> (<\/span><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">1<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> E2 a, E2 b), <\/span><span style=\"color: #7F848E; font-style: italic\">-- 100 * 100 = 10,000 rows<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    Tally(N) <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> (<\/span><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">ROW_NUMBER<\/span><span style=\"color: #ABB2BF\">() <\/span><span style=\"color: #C678DD\">OVER<\/span><span style=\"color: #ABB2BF\"> (<\/span><span style=\"color: #C678DD\">ORDER BY<\/span><span style=\"color: #ABB2BF\"> (<\/span><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">NULL<\/span><span style=\"color: #ABB2BF\">)) <\/span><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> E4)<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">e<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">id<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #D19A66\">e<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">name<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> employees e<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">INNER JOIN<\/span><span style=\"color: #ABB2BF\"> Tally t <\/span><span style=\"color: #C678DD\">ON<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">t<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">N<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">&lt;=<\/span><span style=\"color: #ABB2BF\"> (<\/span><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">COUNT<\/span><span style=\"color: #ABB2BF\">(*) <\/span><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> employees)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">ORDER BY<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">e<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">id<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<div style=\"height:19px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">This method generates a tally table on the fly and joins it with your actual table, effectively creating a loop without procedural code.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Real-World Scenario: Updating Customer Statuses<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Let&#8217;s apply these looping techniques to a real-world scenario. Imagine you need to update customer statuses based on their recent order history:<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#abb2bf;--cbp-line-number-width:calc(2 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#282c34\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" data-code=\"DECLARE @customerId INT, @lastOrderDate DATE, @newStatus VARCHAR(20)\nDECLARE @today DATE = GETDATE()\n\nDECLARE cur CURSOR FOR \nSELECT c.customer_id, MAX(o.order_date) as last_order_date\nFROM customers c\nLEFT JOIN orders o ON c.customer_id = o.customer_id\nGROUP BY c.customer_id\n\nOPEN cur\nFETCH NEXT FROM cur INTO @customerId, @lastOrderDate\n\nWHILE @@FETCH_STATUS = 0\nBEGIN\n    SET @newStatus = \n        CASE \n            WHEN @lastOrderDate IS NULL THEN 'New'\n            WHEN DATEDIFF(day, @lastOrderDate, @today) <= 30 THEN 'Active'\n            WHEN DATEDIFF(day, @lastOrderDate, @today) <= 90 THEN 'At Risk'\n            ELSE 'Inactive'\n        END\n\n    UPDATE customers\n    SET status = @newStatus\n    WHERE customer_id = @customerId\n\n    FETCH NEXT FROM cur INTO @customerId, @lastOrderDate\nEND\n\nCLOSE cur\nDEALLOCATE cur\" style=\"color:#abb2bf;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki one-dark-pro\" style=\"background-color: #282c34\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #C678DD\">DECLARE<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #E06C75\">@customerId<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">INT<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #E06C75\">@lastOrderDate<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">DATE<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #E06C75\">@newStatus<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">VARCHAR<\/span><span style=\"color: #ABB2BF\">(<\/span><span style=\"color: #D19A66\">20<\/span><span style=\"color: #ABB2BF\">)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">DECLARE<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #E06C75\">@today<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">DATE<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">=<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">GETDATE<\/span><span style=\"color: #ABB2BF\">()<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">DECLARE<\/span><span style=\"color: #ABB2BF\"> cur <\/span><span style=\"color: #C678DD\">CURSOR<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">FOR<\/span><span style=\"color: #ABB2BF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">c<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">customer_id<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #56B6C2\">MAX<\/span><span style=\"color: #ABB2BF\">(<\/span><span style=\"color: #D19A66\">o<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">order_date<\/span><span style=\"color: #ABB2BF\">) <\/span><span style=\"color: #C678DD\">as<\/span><span style=\"color: #ABB2BF\"> last_order_date<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> customers c<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">LEFT JOIN<\/span><span style=\"color: #ABB2BF\"> orders o <\/span><span style=\"color: #C678DD\">ON<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">c<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">customer_id<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">=<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">o<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">customer_id<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">GROUP BY<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">c<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">customer_id<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">OPEN<\/span><span style=\"color: #ABB2BF\"> cur<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">FETCH<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">NEXT<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> cur <\/span><span style=\"color: #C678DD\">INTO<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #E06C75\">@customerId<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #E06C75\">@lastOrderDate<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">WHILE<\/span><span style=\"color: #ABB2BF\"> @@FETCH_STATUS <\/span><span style=\"color: #56B6C2\">=<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">0<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">BEGIN<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">SET<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #E06C75\">@newStatus<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">=<\/span><span style=\"color: #ABB2BF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        <\/span><span style=\"color: #C678DD\">CASE<\/span><span style=\"color: #ABB2BF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">            <\/span><span style=\"color: #C678DD\">WHEN<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #E06C75\">@lastOrderDate<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">IS<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">NULL<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">THEN<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #98C379\">&#39;New&#39;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">            <\/span><span style=\"color: #C678DD\">WHEN<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">DATEDIFF<\/span><span style=\"color: #ABB2BF\">(<\/span><span style=\"color: #C678DD\">day<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #E06C75\">@lastOrderDate<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #E06C75\">@today<\/span><span style=\"color: #ABB2BF\">) <\/span><span style=\"color: #56B6C2\">&lt;=<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">30<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">THEN<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #98C379\">&#39;Active&#39;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">            <\/span><span style=\"color: #C678DD\">WHEN<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">DATEDIFF<\/span><span style=\"color: #ABB2BF\">(<\/span><span style=\"color: #C678DD\">day<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #E06C75\">@lastOrderDate<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #E06C75\">@today<\/span><span style=\"color: #ABB2BF\">) <\/span><span style=\"color: #56B6C2\">&lt;=<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">90<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">THEN<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #98C379\">&#39;At Risk&#39;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">            <\/span><span style=\"color: #C678DD\">ELSE<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #98C379\">&#39;Inactive&#39;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        <\/span><span style=\"color: #C678DD\">END<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">UPDATE<\/span><span style=\"color: #ABB2BF\"> customers<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">SET<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">status<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">=<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #E06C75\">@newStatus<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">WHERE<\/span><span style=\"color: #ABB2BF\"> customer_id <\/span><span style=\"color: #56B6C2\">=<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #E06C75\">@customerId<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">FETCH<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">NEXT<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> cur <\/span><span style=\"color: #C678DD\">INTO<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #E06C75\">@customerId<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #E06C75\">@lastOrderDate<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">END<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">CLOSE<\/span><span style=\"color: #ABB2BF\"> cur<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">DEALLOCATE<\/span><span style=\"color: #ABB2BF\"> cur<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<div style=\"height:19px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">This script loops through customers, calculates their last order date, and updates their status accordingly. While this could be done with a single UPDATE statement, the looping approach allows for more complex logic and potential additional processing per customer.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Looping for Data Migration<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Another common use case for looping is data migration. Suppose you&#8217;re moving data from an old schema to a new one, with some transformations along the way:<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#abb2bf;--cbp-line-number-width:calc(2 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#282c34\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" data-code=\"DECLARE @oldId INT, @oldName VARCHAR(50), @oldEmail VARCHAR(100)\nDECLARE @newId INT\n\nDECLARE cur CURSOR FOR SELECT id, name, email FROM old_customers\nOPEN cur\nFETCH NEXT FROM cur INTO @oldId, @oldName, @oldEmail\n\nWHILE @@FETCH_STATUS = 0\nBEGIN\n    -- Insert into new customers table\n    INSERT INTO new_customers (name, email)\n    VALUES (@oldName, @oldEmail)\n\n    SET @newId = SCOPE_IDENTITY()\n\n    -- Migrate related data\n    INSERT INTO new_orders (new_customer_id, order_date, amount)\n    SELECT @newId, order_date, amount\n    FROM old_orders\n    WHERE old_customer_id = @oldId\n\n    FETCH NEXT FROM cur INTO @oldId, @oldName, @oldEmail\nEND\n\nCLOSE cur\nDEALLOCATE cur\" style=\"color:#abb2bf;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki one-dark-pro\" style=\"background-color: #282c34\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #C678DD\">DECLARE<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #E06C75\">@oldId<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">INT<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #E06C75\">@oldName<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">VARCHAR<\/span><span style=\"color: #ABB2BF\">(<\/span><span style=\"color: #D19A66\">50<\/span><span style=\"color: #ABB2BF\">), <\/span><span style=\"color: #E06C75\">@oldEmail<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">VARCHAR<\/span><span style=\"color: #ABB2BF\">(<\/span><span style=\"color: #D19A66\">100<\/span><span style=\"color: #ABB2BF\">)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">DECLARE<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #E06C75\">@newId<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">INT<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">DECLARE<\/span><span style=\"color: #ABB2BF\"> cur <\/span><span style=\"color: #C678DD\">CURSOR<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">FOR<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> id, <\/span><span style=\"color: #C678DD\">name<\/span><span style=\"color: #ABB2BF\">, email <\/span><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> old_customers<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">OPEN<\/span><span style=\"color: #ABB2BF\"> cur<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">FETCH<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">NEXT<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> cur <\/span><span style=\"color: #C678DD\">INTO<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #E06C75\">@oldId<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #E06C75\">@oldName<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #E06C75\">@oldEmail<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">WHILE<\/span><span style=\"color: #ABB2BF\"> @@FETCH_STATUS <\/span><span style=\"color: #56B6C2\">=<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">0<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">BEGIN<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #7F848E; font-style: italic\">-- Insert into new customers table<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">INSERT INTO<\/span><span style=\"color: #ABB2BF\"> new_customers (<\/span><span style=\"color: #C678DD\">name<\/span><span style=\"color: #ABB2BF\">, email)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">VALUES<\/span><span style=\"color: #ABB2BF\"> (<\/span><span style=\"color: #E06C75\">@oldName<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #E06C75\">@oldEmail<\/span><span style=\"color: #ABB2BF\">)<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">SET<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #E06C75\">@newId<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">=<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">SCOPE_IDENTITY<\/span><span style=\"color: #ABB2BF\">()<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #7F848E; font-style: italic\">-- Migrate related data<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">INSERT INTO<\/span><span style=\"color: #ABB2BF\"> new_orders (new_customer_id, order_date, amount)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #E06C75\">@newId<\/span><span style=\"color: #ABB2BF\">, order_date, amount<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> old_orders<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">WHERE<\/span><span style=\"color: #ABB2BF\"> old_customer_id <\/span><span style=\"color: #56B6C2\">=<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #E06C75\">@oldId<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">FETCH<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">NEXT<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> cur <\/span><span style=\"color: #C678DD\">INTO<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #E06C75\">@oldId<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #E06C75\">@oldName<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #E06C75\">@oldEmail<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">END<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">CLOSE<\/span><span style=\"color: #ABB2BF\"> cur<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">DEALLOCATE<\/span><span style=\"color: #ABB2BF\"> cur<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<div style=\"height:19px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">This script migrates customer data from an old schema to a new one, including related order data. The loop allows for complex transformations and handling of related data that might be difficult to achieve in a single set-based operation.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Using Temp Tables for Efficient Looping<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">When dealing with large datasets, using temporary tables can improve performance:<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#abb2bf;--cbp-line-number-width:calc(2 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#282c34\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" data-code=\"-- Create a temp table with the data to process\nSELECT id, name, email\nINTO #CustomersToProcess\nFROM customers\nWHERE last_update < DATEADD(day, -30, GETDATE())\n\nDECLARE @id INT, @name VARCHAR(50), @email VARCHAR(100)\n\nWHILE EXISTS (SELECT 1 FROM #CustomersToProcess)\nBEGIN\n    -- Get the first row\n    SELECT TOP 1 @id = id, @name = name, @email = email\n    FROM #CustomersToProcess\n\n    -- Process the customer (e.g., send an email)\n    EXEC sp_SendEmail @email, 'Welcome Back!', 'We miss you...'\n\n    -- Remove the processed row\n    DELETE FROM #CustomersToProcess WHERE id = @id\nEND\n\nDROP TABLE #CustomersToProcess\" style=\"color:#abb2bf;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki one-dark-pro\" style=\"background-color: #282c34\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #7F848E; font-style: italic\">-- Create a temp table with the data to process<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> id, <\/span><span style=\"color: #C678DD\">name<\/span><span style=\"color: #ABB2BF\">, email<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">INTO<\/span><span style=\"color: #ABB2BF\"> #CustomersToProcess<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> customers<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">WHERE<\/span><span style=\"color: #ABB2BF\"> last_update <\/span><span style=\"color: #56B6C2\">&lt;<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">DATEADD<\/span><span style=\"color: #ABB2BF\">(<\/span><span style=\"color: #C678DD\">day<\/span><span style=\"color: #ABB2BF\">, -<\/span><span style=\"color: #D19A66\">30<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #56B6C2\">GETDATE<\/span><span style=\"color: #ABB2BF\">())<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">DECLARE<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #E06C75\">@id<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">INT<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #E06C75\">@name<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">VARCHAR<\/span><span style=\"color: #ABB2BF\">(<\/span><span style=\"color: #D19A66\">50<\/span><span style=\"color: #ABB2BF\">), <\/span><span style=\"color: #E06C75\">@email<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">VARCHAR<\/span><span style=\"color: #ABB2BF\">(<\/span><span style=\"color: #D19A66\">100<\/span><span style=\"color: #ABB2BF\">)<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">WHILE<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">EXISTS<\/span><span style=\"color: #ABB2BF\"> (<\/span><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">1<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> #CustomersToProcess)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">BEGIN<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #7F848E; font-style: italic\">-- Get the first row<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">TOP<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">1<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #E06C75\">@id<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">=<\/span><span style=\"color: #ABB2BF\"> id, <\/span><span style=\"color: #E06C75\">@name<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">=<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">name<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #E06C75\">@email<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">=<\/span><span style=\"color: #ABB2BF\"> email<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> #CustomersToProcess<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #7F848E; font-style: italic\">-- Process the customer (e.g., send an email)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">EXEC<\/span><span style=\"color: #ABB2BF\"> sp_SendEmail <\/span><span style=\"color: #E06C75\">@email<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #98C379\">&#39;Welcome Back!&#39;<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #98C379\">&#39;We miss you...&#39;<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #7F848E; font-style: italic\">-- Remove the processed row<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">DELETE<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> #CustomersToProcess <\/span><span style=\"color: #C678DD\">WHERE<\/span><span style=\"color: #ABB2BF\"> id <\/span><span style=\"color: #56B6C2\">=<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #E06C75\">@id<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">END<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">DROP<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">TABLE<\/span><span style=\"color: #ABB2BF\"> #CustomersToProcess<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<div style=\"height:19px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">This approach is particularly useful when you need to process a subset of a large table efficiently.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Set-Based Alternatives to Looping<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Before resorting to loops, consider if a set-based approach might work. Here&#8217;s an example of updating customer statuses without a loop:<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#abb2bf;--cbp-line-number-width:calc(2 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#282c34\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" data-code=\"WITH CustomerLastOrder AS (\n    SELECT c.customer_id, MAX(o.order_date) as last_order_date\n    FROM customers c\n    LEFT JOIN orders o ON c.customer_id = o.customer_id\n    GROUP BY c.customer_id\n)\nUPDATE c\nSET c.status = \n    CASE \n        WHEN clo.last_order_date IS NULL THEN 'New'\n        WHEN DATEDIFF(day, clo.last_order_date, GETDATE()) <= 30 THEN 'Active'\n        WHEN DATEDIFF(day, clo.last_order_date, GETDATE()) <= 90 THEN 'At Risk'\n        ELSE 'Inactive'\n    END\nFROM customers c\nJOIN CustomerLastOrder clo ON c.customer_id = clo.customer_id\" style=\"color:#abb2bf;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki one-dark-pro\" style=\"background-color: #282c34\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #C678DD\">WITH<\/span><span style=\"color: #ABB2BF\"> CustomerLastOrder <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> (<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">c<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">customer_id<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #56B6C2\">MAX<\/span><span style=\"color: #ABB2BF\">(<\/span><span style=\"color: #D19A66\">o<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">order_date<\/span><span style=\"color: #ABB2BF\">) <\/span><span style=\"color: #C678DD\">as<\/span><span style=\"color: #ABB2BF\"> last_order_date<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> customers c<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">LEFT JOIN<\/span><span style=\"color: #ABB2BF\"> orders o <\/span><span style=\"color: #C678DD\">ON<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">c<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">customer_id<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">=<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">o<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">customer_id<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">GROUP BY<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">c<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">customer_id<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">UPDATE<\/span><span style=\"color: #ABB2BF\"> c<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">SET<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">c<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">status<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">=<\/span><span style=\"color: #ABB2BF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">CASE<\/span><span style=\"color: #ABB2BF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        <\/span><span style=\"color: #C678DD\">WHEN<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">clo<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">last_order_date<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">IS<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">NULL<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">THEN<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #98C379\">&#39;New&#39;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        <\/span><span style=\"color: #C678DD\">WHEN<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">DATEDIFF<\/span><span style=\"color: #ABB2BF\">(<\/span><span style=\"color: #C678DD\">day<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #D19A66\">clo<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">last_order_date<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #56B6C2\">GETDATE<\/span><span style=\"color: #ABB2BF\">()) <\/span><span style=\"color: #56B6C2\">&lt;=<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">30<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">THEN<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #98C379\">&#39;Active&#39;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        <\/span><span style=\"color: #C678DD\">WHEN<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">DATEDIFF<\/span><span style=\"color: #ABB2BF\">(<\/span><span style=\"color: #C678DD\">day<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #D19A66\">clo<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">last_order_date<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #56B6C2\">GETDATE<\/span><span style=\"color: #ABB2BF\">()) <\/span><span style=\"color: #56B6C2\">&lt;=<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">90<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">THEN<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #98C379\">&#39;At Risk&#39;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        <\/span><span style=\"color: #C678DD\">ELSE<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #98C379\">&#39;Inactive&#39;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">END<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> customers c<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">JOIN<\/span><span style=\"color: #ABB2BF\"> CustomerLastOrder clo <\/span><span style=\"color: #C678DD\">ON<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">c<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">customer_id<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">=<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">clo<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">customer_id<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<div style=\"height:19px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">This set-based approach is often more efficient than looping, especially for large datasets.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Handling Errors in Loops<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">When looping through tables, it&#8217;s crucial to handle potential errors gracefully. Here&#8217;s an example using TRY\u2026CATCH:<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#abb2bf;--cbp-line-number-width:calc(2 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#282c34\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" data-code=\"DECLARE @customerId INT\n\nDECLARE cur CURSOR FOR SELECT customer_id FROM customers\nOPEN cur\nFETCH NEXT FROM cur INTO @customerId\n\nWHILE @@FETCH_STATUS = 0\nBEGIN\n    BEGIN TRY\n        -- Attempt some operation that might fail\n        EXEC sp_UpdateCustomerDetails @customerId\n\n        -- If successful, log it\n        INSERT INTO operation_log (customer_id, operation, status)\n        VALUES (@customerId, 'UpdateDetails', 'Success')\n    END TRY\n    BEGIN CATCH\n        -- Log the error\n        INSERT INTO error_log (customer_id, error_message)\n        VALUES (@customerId, ERROR_MESSAGE())\n    END CATCH\n\n    FETCH NEXT FROM cur INTO @customerId\nEND\n\nCLOSE cur\nDEALLOCATE cur\" style=\"color:#abb2bf;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki one-dark-pro\" style=\"background-color: #282c34\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #C678DD\">DECLARE<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #E06C75\">@customerId<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">INT<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">DECLARE<\/span><span style=\"color: #ABB2BF\"> cur <\/span><span style=\"color: #C678DD\">CURSOR<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">FOR<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> customer_id <\/span><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> customers<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">OPEN<\/span><span style=\"color: #ABB2BF\"> cur<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">FETCH<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">NEXT<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> cur <\/span><span style=\"color: #C678DD\">INTO<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #E06C75\">@customerId<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">WHILE<\/span><span style=\"color: #ABB2BF\"> @@FETCH_STATUS <\/span><span style=\"color: #56B6C2\">=<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">0<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">BEGIN<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">BEGIN<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">TRY<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        <\/span><span style=\"color: #7F848E; font-style: italic\">-- Attempt some operation that might fail<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        <\/span><span style=\"color: #C678DD\">EXEC<\/span><span style=\"color: #ABB2BF\"> sp_UpdateCustomerDetails <\/span><span style=\"color: #E06C75\">@customerId<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        <\/span><span style=\"color: #7F848E; font-style: italic\">-- If successful, log it<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        <\/span><span style=\"color: #C678DD\">INSERT INTO<\/span><span style=\"color: #ABB2BF\"> operation_log (customer_id, operation, <\/span><span style=\"color: #C678DD\">status<\/span><span style=\"color: #ABB2BF\">)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        <\/span><span style=\"color: #C678DD\">VALUES<\/span><span style=\"color: #ABB2BF\"> (<\/span><span style=\"color: #E06C75\">@customerId<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #98C379\">&#39;UpdateDetails&#39;<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #98C379\">&#39;Success&#39;<\/span><span style=\"color: #ABB2BF\">)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">END<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">TRY<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">BEGIN<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">CATCH<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        <\/span><span style=\"color: #7F848E; font-style: italic\">-- Log the error<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        <\/span><span style=\"color: #C678DD\">INSERT INTO<\/span><span style=\"color: #ABB2BF\"> error_log (customer_id, error_message)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        <\/span><span style=\"color: #C678DD\">VALUES<\/span><span style=\"color: #ABB2BF\"> (<\/span><span style=\"color: #E06C75\">@customerId<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #56B6C2\">ERROR_MESSAGE<\/span><span style=\"color: #ABB2BF\">())<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">END<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">CATCH<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">FETCH<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">NEXT<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> cur <\/span><span style=\"color: #C678DD\">INTO<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #E06C75\">@customerId<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">END<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">CLOSE<\/span><span style=\"color: #ABB2BF\"> cur<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">DEALLOCATE<\/span><span style=\"color: #ABB2BF\"> cur<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<div style=\"height:19px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">This approach ensures that an error in processing one row doesn&#8217;t stop the entire operation.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Conclusion: Using SQL Table Looping<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">While SQL is optimized for set-based operations, looping through tables has its place in a SQL developer&#8217;s toolkit. We&#8217;ve covered:<\/p>\n\n\n\n<ol class=\"wp-block-list\">\n<li>Traditional cursor-based looping<\/li>\n\n\n\n<li>WHILE loops with ROW_NUMBER()<\/li>\n\n\n\n<li>Using tally tables for pseudo-loops<\/li>\n\n\n\n<li>Real-world applications in customer management and data migration<\/li>\n\n\n\n<li>Efficient looping with temporary tables<\/li>\n\n\n\n<li>Set-based alternatives to consider before looping<\/li>\n\n\n\n<li>Error handling in SQL loops<\/li>\n<\/ol>\n\n\n\n<p class=\"wp-block-paragraph\">Remember, the best approach depends on your specific use case, data volume, and performance requirements. While loops can solve complex problems, always consider if a set-based solution might be more efficient. <\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<div class=\"wp-block-group\"><div class=\"wp-block-group__inner-container is-layout-constrained wp-block-group-is-layout-constrained\">\n<h2 class=\"wp-block-heading\">FAQ<\/h2>\n\n\n<div id=\"rank-math-faq\" class=\"rank-math-block\">\n<ol class=\"rank-math-list \">\n<li id=\"faq-question-1771985858796\" class=\"rank-math-list-item\">\n<h3 class=\"rank-math-question \"><strong><strong><strong>When should you loop through a table in SQL instead of using a set-based query?<\/strong><\/strong><\/strong><\/h3>\n<div class=\"rank-math-answer \">\n\n<p>Looping is useful when you need row-by-row processing, such as applying complex logic, performing sequential operations, or handling tasks like data migration or custom updates that can\u2019t easily be done in a single query.<\/p>\n\n<\/div>\n<\/li>\n<li id=\"faq-question-1771985909936\" class=\"rank-math-list-item\">\n<h3 class=\"rank-math-question \"><strong><strong><strong><strong>What is the most common way to loop through rows in SQL?<\/strong><\/strong><\/strong><\/strong><\/h3>\n<div class=\"rank-math-answer \">\n\n<p>The traditional approach is using <strong>cursors<\/strong>, which fetch and process one row at a time. They\u2019re straightforward but can be slower on large datasets due to overhead.<\/p>\n\n<\/div>\n<\/li>\n<li id=\"faq-question-1771985985770\" class=\"rank-math-list-item\">\n<h3 class=\"rank-math-question \"><strong><strong><strong>Are there more efficient alternatives to cursors for looping?<\/strong><\/strong><\/strong><\/h3>\n<div class=\"rank-math-answer \">\n\n<p>Yes. The article explains options like <strong>WHILE loops with <code>ROW_NUMBER()<\/code><\/strong>, <strong>tally tables<\/strong>, and <strong>temporary tables<\/strong>, which can reduce overhead and improve performance depending on the scenario.<\/p>\n\n<\/div>\n<\/li>\n<li id=\"faq-question-1771985994514\" class=\"rank-math-list-item\">\n<h3 class=\"rank-math-question \"><strong><strong><strong><strong>What are some real-world use cases for looping through tables?<\/strong><\/strong><\/strong><\/strong><\/h3>\n<div class=\"rank-math-answer \">\n\n<p>Common examples include updating customer statuses based on activity, migrating data between schemas, and running custom logic for each record during batch processing.<\/p>\n\n<\/div>\n<\/li>\n<li id=\"faq-question-1771986003404\" class=\"rank-math-list-item\">\n<h3 class=\"rank-math-question \"><strong><strong><strong><strong>Why should you consider set-based solutions before using loops?<\/strong><\/strong><\/strong><\/strong><\/h3>\n<div class=\"rank-math-answer \">\n\n<p>SQL is optimized for set-based operations, which are usually faster and more scalable. Loops should be used only when the logic requires sequential processing or can\u2019t be efficiently expressed in a single query.<\/p>\n\n<\/div>\n<\/li>\n<\/ol>\n<\/div><\/div><\/div>\n","protected":false},"excerpt":{"rendered":"<p>SQL Loop Through Table Fully Explained Looping through tables in SQL might seem counterintuitive at first. After all, SQL is designed for set-based operations, not procedural programming. However, there are scenarios where row-by-row processing is necessary or even more efficient. Let&#8217;s explore various techniques to loop through tables in SQL, complete with practical examples and [&hellip;]<\/p>\n","protected":false},"author":11,"featured_media":4224,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":true,"_eb_attr":"","_uag_custom_page_level_css":"","footnotes":""},"categories":[21],"tags":[],"class_list":["post-4223","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-blog"],"acf":[],"uagb_featured_image_src":{"full":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/10\/Five.Co-SQL-Loop-Through-Table-Practical-Guide.jpg",1280,720,false],"thumbnail":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/10\/Five.Co-SQL-Loop-Through-Table-Practical-Guide-150x150.jpg",150,150,true],"medium":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/10\/Five.Co-SQL-Loop-Through-Table-Practical-Guide-300x169.jpg",300,169,true],"medium_large":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/10\/Five.Co-SQL-Loop-Through-Table-Practical-Guide-768x432.jpg",768,432,true],"large":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/10\/Five.Co-SQL-Loop-Through-Table-Practical-Guide-1024x576.jpg",1024,576,true],"1536x1536":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/10\/Five.Co-SQL-Loop-Through-Table-Practical-Guide.jpg",1280,720,false],"2048x2048":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/10\/Five.Co-SQL-Loop-Through-Table-Practical-Guide.jpg",1280,720,false]},"uagb_author_info":{"display_name":"Ryan Forrester","author_link":"https:\/\/preview-php85.dzine.org\/five\/author\/f1v5_ed9_au\/"},"uagb_comment_info":0,"uagb_excerpt":"SQL Loop Through Table Fully Explained Looping through tables in SQL might seem counterintuitive at first. After all, SQL is designed for set-based operations, not procedural programming. However, there are scenarios where row-by-row processing is necessary or even more efficient. Let&#8217;s explore various techniques to loop through tables in SQL, complete with practical examples and&hellip;","_links":{"self":[{"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/posts\/4223","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/users\/11"}],"replies":[{"embeddable":true,"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/comments?post=4223"}],"version-history":[{"count":0,"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/posts\/4223\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/media\/4224"}],"wp:attachment":[{"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/media?parent=4223"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/categories?post=4223"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/tags?post=4223"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}