This is my second blog post in as many days where I talk about my love for CROSS APPLY. In this post I will talk about how to simulate a lambda expression using CROSS APPLY. Lambda expressions, also known as anonymous, literal, or inline functions, are a way of describing a function that has no declared name. These are useful when you need to define an expression where its use is limited to a single statement and otherwise impractical to create a user defined function. I say “simulate a lambda” because its utility is limited compared to how real lambda expressions work in other languages, but this can be used for any type of scalar expression you could use in a SELECT statement.

Sample Case

For this example we have a table of dates and we need to compute 3 columns from the DateValue column; YearString – a string in “yyyy” format, MonthString – a string in “mmmm yyyy” format, and DateString – a string in “mmmm dd, yyyy” format. For the purposes of this example, let’s also pretend that T-SQL does not have a FORMAT() function to to this.

We will use the following code to generate our sample data-set:

DECLARE @Dates TABLE (
	DateValue DATE NOT NULL PRIMARY KEY
	);

INSERT @Dates
	VALUES ('0001-01-05')
		, ('2019-01-01')
		, ('2019-01-12');

First let’s consider the how we might do this without a CROSS APPLY:

SELECT DateValue
	, CONCAT(REPLICATE('0', 4 - LEN(DATENAME(YEAR, DateValue))), DATENAME(YEAR, DateValue)) AS YearString 
	, CONCAT(DATENAME(MONTH, DateValue), ' ', CONCAT(REPLICATE('0', 4 - LEN(DATENAME(YEAR, DateValue))), DATENAME(YEAR, DateValue))) AS MonthString
	, CONCAT(DATENAME(MONTH, DateValue), ' ', CONCAT(REPLICATE('0', 2 - LEN(DATENAME(DAY, DateValue))), DATENAME(DAY, DateValue)), ', ', CONCAT(REPLICATE('0', 4 - LEN(DATENAME(YEAR, DateValue))), DATENAME(YEAR, DateValue))) AS DateString
FROM @Dates;

First of all, this looks like a mess. Even with further formatting, it would be hard to figure out what it actually does let alone troubleshoot it. Also, many of the same operations are copy-pasted multiple times, such as padding the year with leading zeros 4 times. It would be nice if we write some of those expressions once and reuse them. Oh wait we can!

Because of the nature of the way that CROSS APPLY works, we can construct our statement so that we use the DateValue from @Dates as the input parameter that we pass to the CROSS APPLY which has three “lambda expressions” and the output of these expressions are a column-set joined to the right of our anchor table. Let’s take a look:

SELECT DateValue
	, YearString
	, CONCAT(NameOfMonth, ' ', YearString) AS MonthString
	, CONCAT(NameOfMonth, ' ', DayString, ', ', YearString) AS DateString
FROM @Dates
CROSS APPLY (
	VALUES (
		CONCAT(REPLICATE('0', 4 - LEN(DATENAME(YEAR, DateValue))), DATENAME(YEAR, DateValue))
		, DATENAME(MONTH, DateValue)
		, CONCAT(REPLICATE('0', 2 - LEN(DATENAME(DAY, DateValue))), DATENAME(DAY, DateValue))
		)
	) AS lambdas(YearString, NameOfMonth, DayString);

Already, it’s looking a lot better. It’s a lot clearer how this works and instead of copying the same operations all over the place we can just reference the “lambda expression” when we need to use it, in this case by referencing the column name.

There are two terms we are reusing inside our expressions, and I wish there was a way to simplify these expressions. Oh wait, You can! The APPLY operator always executes from left-to-right order which means we can use any of the columns from the left-hand side of the APPLY and reference them, including columns we generated from a previous APPLY. Cascading lambda expressions in T-SQL? That’s crazy!

SELECT DateValue
	, YearString
	, CONCAT(NameOfMonth, ' ', YearString) AS MonthString
	, CONCAT(NameOfMonth, ' ', DayString, ', ', YearString) AS DateString
FROM @Dates
CROSS APPLY (
	VALUES(
		DATENAME(YEAR, DateValue)
		, DATENAME(DAY, DateValue)
		)
	) AS lambda1(NameOfYear, NameOfDay)
CROSS APPLY (
	VALUES (
		CONCAT(REPLICATE('0', 4 - LEN(NameOfYear)), NameOfYear)
		, DATENAME(MONTH, DateValue)
		, CONCAT(REPLICATE('0', 2 - LEN(NameOfDay)), NameOfDay)
		)
	) AS lambda2(YearString, NameOfMonth, DayString);

I am forgoing looking at plans and execution times with this example because, in fact, the plans are identical in all three cases.

Conclusions

One thing is clear, when it comes to CROSS APPLY, you can’t stop me.