SQL Server Portal

All of us are familiar with Common Table Expression (CTE) because of its frequent use. We use it in our day-to-day operation quite frequently. Recently, I came across a case where I had to use CTE in a user defined function.

Let me create a sample user defined function (UDF) in Northwind database with the help of CTE to use in my demonstration as shown below.

USE Northwind
GO

CREATE FUNCTION [dbo].[udf_Most_Expensive_Product]
(
	@SupplierID INT
)
RETURNS NVARCHAR(50)
AS
BEGIN
	DECLARE @ProductName NVARCHAR(50)

	;WITH CTE AS
	(
		SELECT ROW_NUMBER() OVER (ORDER BY UnitPrice DESC) AS SNo  
			 , ProductName 
			 , UnitPrice FROM Products 
		WHERE SupplierID = @SupplierID
	)

	SELECT 
			@ProductName = ProductName
	FROM CTE 
	WHERE SNo = 1

	RETURN @ProductName;
END

GO

Let me call the above created user defined function(UDF) and see the results.

USE Northwind
GO

SELECT [dbo].[udf_Most_Expensive_Product](2) AS [ProductName];
GO
--OUTPUT

Conclusion:

It is quite simple to use Common Table Expression (CTE) in user defined function (UDF).