Using a recursive common table expression, to calculate totals and subtotals of children and grandchildren in a hierarchy

In SQL Server a common table expression can be used to calculate totals / subtotals of a table containing hierarchical data:   — Create test data: if object_id(‘tempdb..#Hierarchy’) is not null begin drop table #Hierarchy end create table #Hierarchy ( Id int not null, ParentId int null, Amount int not …

Change varchar column length, when column does not match given length in T-SQL.

The following code will update a column to varchar(max) only, when it does not already have the varchar(max) length. — Update column ‘Name’ length, it should be varchar(max). — Note: varchar(max) has length ‘-1’. if ( ( select character_maximum_length from information_schema.columns where table_name=object_name(object_id(‘dbo.Table1’)) and COLUMN_NAME = ‘Name’ ) <> -1 …

How to get the amount of elapsed minutes in current quarter in T-SQL

If you want to know the amount of elapsed minutes in the current quarter, you can use the following T-SQL code: declare @CurrentDateTime datetime = ‘2014-03-27 10:56:10’ declare @MinutesPassedInHour int = convert(int, datepart(minute, @CurrentDateTime)) declare @quarterCount int = @MinutesPassedInHour / 15 declare @MinutesPassedInQuarter int = (@MinutesPassedInHour – (@quarterCount * 15)) …

T-SQL tip: log error details in catch

If you want to log detailed information on an error in a catch block, you can use the following code:   BEGIN CATCH declare @Message varchar(2048) = ‘<< Your custom error text here. >>’ + ‘Number: ‘ + cast(isnull(error_number(), ”) as varchar(50)) + ‘ ‘ + ‘Severity: ‘ + cast(isnull(error_severity(), …