26 items found
- Open the Table Browser with a Bookmarklet | Dynamics 365 Finance & Operations
- Get Choice Label / Option Set Label (Dataverse Reporting)
What are Choices in Dataverse? Also, and formerly, known as Option Sets and Picklists, Choices are effectively a key-value pair object type in Dataverse that allow for a drop-down list of static options. The end user sees a label and the database stores its corresponding integer value. This is wonderful for application performance but poses a problem when reporting. The Problem While integers are great for performance in the application, they are not so helpful for reporting. Depending on how the data is extracted from the application, one may encounter only having the value of the integer with no label. Users of reports will not want to see the integer value, but the label that they are familiar with in the application. Thus, it is regularly desired to show the label. But how does one retrieve the label in a repeatable way? To address this problem, a SQL function can be used. The Function The function below is an in-line table valued function. This allows for a repeatable, straightforward way to retrieve a label. Not only is it repeatable but using an in-line table valued function instead of a scalar function will yield performant queries over any size of data. Some other solutions using scalar functions in SQL do not scale as well over large data sets. The function assumes using a single language is used. If needing to use multiple languages, need to pass in the language into the function as well. Otherwise leave it hardcoded as 1033 (English) CREATE FUNCTION [dbo].[GetChoiceLabel] ( @EntityName nvarchar(64), @ChoiceName nvarchar(64), @ChoiceValue int ) RETURNS TABLE AS RETURN ( SELECT StringMap.Value AS Label FROM dbo.StringMap AS StringMap WHERE StringMap.AttributeName=@ChoiceName AND StringMap.ObjectTypeCode=( SELECT TOP 1 Entity.ObjectTypeCode FROM dbo.Entity AS Entity WHERE Entity.LogicalName=@EntityName ) AND StringMap.AttributeValue = @ChoiceValue AND StringMap.langid=1033 ) Notes The above function may need to be updated for your exact situation. Using the Dataverse connector in Azure Data Factory does not require the "SELECT TOP 1" to translate the entity name to an objecttypecode. The Implementation To implement the above solution, you use the APPLY operator (similar to a join). APPLY dbo.GetChoiceLabel('Entity', 'Choice Name', Value Field) AS ChoiceLabel Here is another example using a table called item SELECT I.ItemType, ItemType.Label AS ItemTypeLabel FROM dbo.item as I APPLY dbo.GetChoiceLabel('item','itemType',Item.ItemType) AS ItemType Alternatives There are some alternatives to using an inline function. See a quick overview below.
- 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.
- Joe Bonomo | Technology, Microsoft, Power Platform, SQL | Wheaton
34 minutes ago 2 min Open the Table Browser with a Bookmarklet | Dynamics 365 Finance & Operations Jul 8, 2021 3 min GoTo Looping in SQL FEATURED POSTS Never Miss a New Post. Enter your email Subscribe Thanks for subscribing! THOUGHTS 34 minutes ago 2 min Open the Table Browser with a Bookmarklet | Dynamics 365 Finance & Operations Open the Dynamics 365 Finance and Operations table browser without installing an extension! 1 view 0 comments 1 like. Post not marked as liked 1 Dec 9, 2021 2 min Get Choice Label / Option Set Label (Dataverse Reporting) What are Choices in Dataverse? Also, and formerly, known as Option Sets and Picklists, Choices are effectively a key-value pair object... 14 views 0 comments 1 like. Post not marked as liked 1 Jul 8, 2021 3 min GoTo Looping in SQL Overview of GoTo looping technique in Microsoft SQL Server 26 views 0 comments 1 like. Post not marked as liked 1 Jun 25, 2021 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! 6 views 0 comments 1 like. Post not marked as liked 1 Jun 5, 2021 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... 2 views 0 comments Post not marked as liked Jun 4, 2021 1 min Dynamics 365 F&O Enums | Project | Project Type As a helpful quick reference to the production order status. 5 views 0 comments 1 like. Post not marked as liked 1 Jun 4, 2021 1 min Dynamics 365 F&O Enums | Production Order | Production Order Status As a helpful quick reference to the production order status. 8 views 0 comments 1 like. Post not marked as liked 1 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
- XRM Solution Dependency Checker (XRM Toolbox Plugin)
XRM Solution Dependency Checker (XRM Toolbox Plugin) 2018 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. Over 30k downloads. < Previous Work Go to plugin page Next Work >
- Go To Looper
Go To Looper 2021 Article I wrote for SQL Server Central (750,000+ subscribers) regarding a technique in SQL Server using GoTo for looping. < Previous Work Go to website Next Work >