Digital Increment

A few days ago I figured I’d add to my SQL repertoire and lo and behold I found that Microsoft added a few new SQL constructs to the T-SQL language. I remember how I felt about using the MERGE construct and in some instances it was very useful, but when I discovered IIF , CHOOSE , & THROW ( amongst some of the others like PARSE , CONCAT & ROWS ) I knew that I found some new toys. Let’s take a look at IIF , CHOOSE , & THROW . IIF If you’ve been using expressions in SSIS or VB.Net or even VBA, you should be familiar with “Inline If”.  This function is prototyped as such: IIF([boolean evaluation], [return value if evaluation is true], [return value if evaluation is false]) This function is awesome as the evaluation can be assigned to a variable.  There are SQL constructs like ISNULL , but IIF is way more effective when writing more robust & dynamic SQL scripts.  It can be used such that: DECLARE @c INT = 10 ...

Recursion is a process that was taught in computer science to traverse large datasets as an alternative to loops. When traversing linked lists and stacks recursion was used in C++, but recursion can be implemented in most programming and scripting languages.  Nowadays, recursion is most effective in traversing XML documents, database relationships, or perhaps solving equations.  For this situation, we’ll implement recursion in a SQL Server stored procedure.  This will be simple addition up to 31 as SQL Server has set limits on outside procedure calls (the database doesn’t want you to call more than 32 methods/instructions.  When it comes to the stack in SQL Server, it wants to run pretty lean and too many calls to external processes can cause memory problems in SQL Server; the @@NESTLEVEL refers to how many function calls are made in SQL Server). create procedure dbo . recursive_stack ( @i INT ) -- dbo.recursive_stack 23 AS begin if @@...