16 items found
- GoTo Looping in SQL
Looping over a set of records is a very common operation conducted in applications, integrations, data warehouse, reporting, and more. It is generally advised to avoid looping in SQL if possible, as other programming languages can be much more efficient. Alas, there are times looping in SQL either unavoidable or the best option given a myriad of factors. There are various options for looping such as...... WHILE loop Recursive CTE CURSOR Nested stored procedures GoTo looping ......to name a few. This article will focus on the last bullet above that will be referred to as "GoTo Looping". GoTo, What is it? GoTo in Microsoft SQL Server is a keyword in the control flow of a SQL stored procedure. It allows logic to "go to" another point in the code. This effectively allows for jumping around the code and skipping/exiting conditions in an easy way. Example: DECLARE @condition1 bit='true'; @condition2 bit='true'; IF @condition1='true' BEGIN GOTO LabelB; END LabelA: GOTO LabelEnd; LabelB: IF @condition2='true' BEGIN GOTO LabelA; END LabelEnd: return; -- do nothing and exit procedure In the above example, the code would first pass through checking condition 1. It finds that this condition is true, thus it goes to LabelB. It completely skips LabelA code. Once the code in LabelB executes, it would then jump back up to LabelA due to the GOTO in LabelB after it's work is done. Finally, LabelA will go to LabelEnd and return out of the procedure. This is powerful to limit repetition (i.e. have D.R.Y. code). Some criticisms state that this makes the code harder to read, which is a valid point, but situationally dependent. Read more in the official Microsoft doc here. Another use of this technique is for looping. Read on to find out how GOTO can be used to loop over logic GoTo Looping Situation is as follows. A stored procedure is required to be executed for as many legal entities as business has, which will be referred to here after as "companies". It must be run one at a time. The benefit to doing this in SQL is the ease of debugging, logging each execution to a table, and rendering each result set. The solution below could be refactored to use a WHILE loop. When choosing between techniques, it is important to consider all the factors that contribute to SQL performance, code maintainability, and ultimately meeting the requirement. As a general statement, WHILE loops and GOTO loops have negligible performance differences. Example: The example below first sets variables to be used in the loop. Second it sets the company and inserts all companies into a table variable (this assumed a smaller number of companies, else a temporary table should be used). Finally, it loops through each company executing a stored procedure named procedure1 and logging to a table named ExecutionLog /* ========================================== Variable Declaration and Startup logic ========================================== */ --Declare variables for looping DECLARE @continue bit='true', @companycount int=0, @company nvarchar(8)=@company, @executionid uniqueidentifier=NewID(), @message nvarchar(4000) DECLARE @companylist TABLE(CompanyID nvarchar(8)); /* List of companies */ --Log execution EXECUTE dbo.usp_writeLog @ExecutionID=@executionid, @Company=@company, @StepName='Begin', @ExecutionStatus='Success', @ExecutionDescription='Process Begin' /* ========================================== Loop through company ========================================== */ INSERT INTO @companylist(CompanyID) SELECT Company=CL.CompanyID FROM dbo.CompanyList AS CL SetCompany: SELECT TOP 1 @company=CompanyID FROM @companylist DELETE FROM @companylist WHERE CompanyID=@company; SELECT @companycount=COUNT(*) FROM @companylist IF @companycount<=0 BEGIN SET @continue='false'; END --Log Execution EXECUTE dbo.usp_writeLog @ExecutionID=@executionid, @Company=@company, @StepName='Set Company', @ExecutionStatus='Success', @ExecutionDescription='Set the company for a multi-company execution' GOTO ExecuteLoop; RETURN; /* ========================================== Execute procedure by company ========================================== */ ExecuteLoop: --Project Direct Source BEGIN TRY EXECUTE dbo.usp_procedure1 @company=@company; END TRY BEGIN CATCH --Log error SET @message=ERROR_MESSAGE(); EXECUTE dbo.usp_writeLog @ExecutionID=@executionid, @Company=@company, @StepName='Procedure 1 Failed', @ExecutionStatus='FAIL', @ExecutionDescription=@message; END CATCH IF @continue='false' BEGIN DECLARE @failureFlag int; SELECT @failureFlag=COUNT(*) FROM dbo.ExecutionLog as EL WHERE EL.ExecutionID=@executionid AND EL.ExecutionStatus='FAIL' IF @failureFlag>0 BEGIN THROW 900705, 'FAILURE OCCURRED', 1; END ELSE BEGIN RETURN; END END ELSE BEGIN GOTO SetCompany; END Conclusion GoTo Looping, as shown in the above example, shows a technique in SQL to loop that is not well known. WHILE loops, cursors, recursive CTEs, and other options are much more well known. Why can the GoTo looping code be preferred? It somewhat comes down to a matter of preference. As an opinion, I have found this technique easier to work with than WHILE loops as there are more flexible scenarios to exiting loops and rejoining the loop using the GoTo technique. This certainly is debatable.
- Quick Tip: SQL Transaction Log Management
The topic of truncating SQL transaction logs is a regular point of confusion. Two issues normally arise, and are the reason for this post. Transaction Log Full Error For anyone working with SQL long enough they have undoubtedly encountered the following error in an application, integration, reporting query, etc. The transaction log for database 'db_name' is full due to 'LOG_BACKUP' This error occurs when the transaction log is full and can no longer grow for various reasons. Transaction Log Consumes Giga/terabytes of space In some cases, the above error is never encountered because the drive that the transaction log resides on either contains enough space to allow the log to grow. Whether the drive was pre-sized or a system administrator expands it, this can lead to huge transaction log sizes. In some cases, the transaction log may be GB or TB larger than the entire database itself! Below are two options to address these issues. Option 1 | Simple Recovery One way to make sure that the SQL transaction log does not grow out of control is to set the database to SIMPLE recovery model from FULL recovery model. The SIMPLE recovery model truncates the transaction log once a transaction completes. The benefit Since the log is being truncated as transactions are performed, the log never grows out of control. The downside Due to the transaction log being truncated in "real-time", it is only possible to recover to the last full backup performed. This works for some use cases and not for others. Cases where this works well Data warehousing (that can be completely rebuilt from scratch) "Scripting" databases that only hold scripts/functions Development and test environments Cases where this does not work well Production Application databases (OLTP) Data warehousing that cannot be rebuilt from scratch Option 2 | Backup Transaction Log The second option to ensure the transaction log does not grow out of control is to properly backup the log. It is suggested to use SIMPLE recovery when it makes sense. With that said, this is not always possible as outlined above. If FULL recovery should be used, backing up the transaction log regularly will truncate the log. Note, this is backing up the TRANSACTION LOG itself, not backing up the data. While backing up the transaction log should be a part of any disaster recovery plan, sometimes only the data is backed up with full backups. This will lead to the transaction log growing out of control and encountering the issues mentioned at the beginning of this post. Conclusion Transaction log management is easy to implement using the various options above, yet easy to get wrong or overlook. Make life easy and use simple recovery or backup the transaction log to ensure the log size does not grow out of control.
- In case you missed it: Microsoft Buys Corp.com
A critical cybersecurity action from Microsoft to help the masses went relatively unnoticed. See the below article from ZDNet describing how Microsoft saved a potential disaster for unsophisticated IT organizations. One excerpt that is particularly frightening "...this means that whoever controls corp.com can passively intercept private communications from hundreds of thousands of computers that end up being taken outside of a corporate environment which uses this 'corp' designation for its Active Directory domain." Could you imagine the consequences if a nefarious actor purchased this domain given the sophisticated hacking operations now-a-days! Wow. Read more below Microsoft buys corp.com domain in the name of customer security | ZDNet
- joe.bonomo | Technology, Microsoft, Power Platform, Consulting, SQL | Wheaton
Jul 8 3 min GoTo Looping in SQL Apr 16 6 min DATA ANALYTICS AND POWER BI – TOP QUESTIONS ANSWERED BY IT AND ACCOUNTING EXPERTS! FEATURED POSTS Never Miss a New Post. Enter your email Subscribe Thanks for subscribing! THOUGHTS Jul 8 3 min GoTo Looping in SQL Overview of GoTo looping technique in Microsoft SQL Server 19 views 0 comments Post not marked as liked Jun 25 2 min Quick Tip: SQL Transaction Log Management Transaction log growing out of control? Encountering error "The transaction log for database is full" Find out how to fix in this post! 4 views 0 comments Post not marked as liked Jun 5 1 min In case you missed it: Microsoft Buys Corp.com A critical cybersecurity action from Microsoft to help the masses went relatively unnoticed. See the below article from ZDNet describing... 1 view 0 comments Post not marked as liked Jun 4 1 min Dynamics 365 F&O Enums | Project | Project Type As a helpful quick reference to the production order status. 2 views 0 comments Post not marked as liked Jun 4 1 min Dynamics 365 F&O Enums | Production Order | Production Order Status As a helpful quick reference to the production order status. 6 views 0 comments Post not marked as liked Jun 4 1 min Dynamics 365 F&O Enums | LedgerPostingType As a helpful quick reference to all the ledger posting types that define the type of transaction posted to the General Ledger in Dynamics... 30 views 0 comments Post not marked as liked Jun 4 1 min Dynamics 365 F&O Enums | InventTrans As a helpful quick reference, here are the enum labels and values for key InventTrans fields. InventTransType This corresponds to the... 17 views 0 comments Post not marked as liked More Posts ABOUT ME Technology consultant with a focus on business applications. I drive organizational change, solve business problems, and improve processes using technology and creative problem solving techniques. Excels in the design, architecture, development, implementation, and support of business application solutions. Utilizes in-depth technical knowledge, combined with expertise across a broad range of technologies, businesses, and industries, to solve client challenges. Experience partnering with entire client organizations, from manufacturing floor workers, to accounts receivable clerks, to C-level executives. Manages all aspects of the software development lifecycle as a key resource and adviser to both clients and team members. Specialties: -Business Application Design & Architecture -Requirements Gathering -Business Systems Analysis -Power Platform -Microsoft 365 -Dynamics 365 -Azure -Teams -Data Analysis -Business Process Improvement -SQL Server (SSIS, SSRS, T-SQL) Read More > SUBSCRIBE Enter your email Subscribe Thanks for subscribing! Favorite Blogs Name Focus CRM Tip of the Day Microsoft Dynamics, Power Platform Corterra Solutions Microsoft Power Platform, Teams, Microsoft 365, Azure SQL Authority (Pinal Dave) SQL Server Jukka Niranen Microsoft Power Platform ("CRM") Brent Ozar SQL Server
- About | joe.bonomo
Problem solver | Business Application Architect | Accomplished SQL Professional
- XRM Solution Dependency Checker (XRM Toolbox Plugin)
XRM Solution Dependency Checker (XRM Toolbox Plugin) 2023 Lead the design of a plugin in the popular XRM Toolbox community tool supporting Microsoft Power Platform (and legacy "xRM"/CRM). Used in the deployment lifecylce to validate that solution contains all necessary dependencies. Eliminated failed solution deployments due to missing dependencies. 1/0 < Previous Work Next Work >