I have briefly explained about user defined function in my previous article. In this article, I will show you how to create a scalar function in SQL Server. A Scalar-valued function returns a single scalar value with multiple numbers of parameters that is why it is called a scalar function or scalar valued function. You can write a bit complex query in it but it must have to return a single value. You also have to mention the return data type and that scalar function must have to return scalar value of that data type.
Create Scalar Function
CREATE FUNCTION dbo.OrdersCount
SELECT @TotalOrders = COUNT(*)
FROM dbo.Orders WITH(NOLOCK)
WHERE CustomerID = @CustomerID
Call Scalar Function
SELECT dbo.OrdersCount(12) AS OrdersCount
You can also use scalar function inside a query.
FROM dbo.Customers WITH(NOLOCK)
WHERE OrdersCount > dbo.OrdersCount(12)