Scalar Valued Functions

A scalar valued function simply returns a single value, such as the result of a mathematical calculation or of formatting some text. Many of SQL Server's in-built functions are based on this, such as returning a date through GETDATE() or returning a string with the trailing blanks removed with the RTRIM() function.

Note

Scalar functions cannot return any of the following data types – cursor, rowversion, timestamp, table, text, ntext, or image. Any other data type is valid.

Scalar functions can form the value for a column within a rowset, return a value that is used as part of a WHERE statement, or simply return a value that is placed in a local variable. Basically, they can be used anywhere a scalar expression can be used.

Note that any T-SQL errors within a function will stop the trigger as well as cancel the invoking statement. For example, if we get a NULL value in an unexpected place and a T-SQL error is thrown, then the function will stop, as will the calling T-SQL, and therefore, our whole stored procedure that invoked the UDF will stop executing.

Calling a UDF is relatively straightforward, when working with scalar values. We just have to provide the name of the function's owner, the function name, and the parameter values (if any) that are to be passed in.

In our previous example (where we added business days), if we wanted to return the value as a column in a result set then we would call the function as:

     SELECT dbo.fn_AddWorkDays(2,'3 March 1998') 

If it were a filter, such as looking for all orders that are two days out, then we could code the T-SQL as:

     SELECT OrderId, CustomerId, RequiredDate     FROM dbo.Orders     WHERE RequiredDate > dbo.fn_AddWorkDays(2,'3 March 1998') 

Or would we? The value returned from this function will always be the same, so, in this instance, it would be better to call the function prior to the SELECT statement, place the value in to a local variable and then use the local variable as the filter. However, in cases where the parameter being passed to the function is going to have a varying value, such as a different value for each row in the rowset, we can't use this approach.

An example of this would be when we are working with different employee names. Another example would be using different dates and amounts for calculating interest. To clarify this further, let's look at a concrete example:

If we wanted to get a list of orders from the Northwind database that shipped more than 10 working days after the order was placed, then the way we can code this is the following:

     SELECT OrderId, CustomerId, OrderDate, ShippedDate     FROM dbo.Orders     WHERE ShippedDate > dbo.fn_AddWorkDays(10,OrderDate) 

Of course, there will be performance degradation with this query, as every row from the Orders table will be passed into the function. One way to improve this would be to add a second condition to the filter for removing rows that would not fit in the returned rowset. In this case, we can add the following conditions to the WHERE statement of the SELECT statement defined above:

     AND ShippedDate IS NOT NULL     AND ShippedDate > DATEADD(dd,10,OrderDate) 

Here, SQL Server will have to take the function into its query plan, although it will be working with a small number of rows, and since we are not completing any further table access within the function itself, the function should not cause any undue overhead. The optimizer will, itself, figure out the best plan to use.

As far as possible, always try to reduce the number of rows by adding in subsequent filter conditions. If we alter the function to include a SELECT statement where we look up the order again and return the OrderDate within the function, it will be slower. Therefore, wherever possible pass in the value from the column, rather than making the function do the work:

     CREATE FUNCTION fn_OrderShipDays (@NoDays INT, @OrderId INT)     RETURNS DATETIME     AS     BEGIN       DECLARE @Today DATETIME       SELECT @Today = OrderDate       FROM dbo.Orders       WHERE OrderId = @OrderId       WHILE @NoDays > 0       ... --the remaining part is same as in the fn_AddWorkDays example 

Scalar functions can be used just as we would use a column value or a local variable within SQL Server, and the same constraints exist for UDFs as for columns and variables. These constraints are:

• If we are using a function as a value in a RETURN statement in a stored procedure, then the function must return an integer number

• When using a function in a PRINT statement the return type must be a string or such that they can be CAST to a character value

• If used as part of an assignment operator on either side of the = sign then the data type should match that on the other side of the equals sign, or be of a data type that can be automatically converted

Scalar functions can also be used in CASE statements, flow control statements, default definitions, and constraints. For a constraint we can access the data in the row in the parameters; with a default definition this is not possible.

Scalar functions are pretty straightforward, but what about if we wanted the function to return a row, or a set of rows of data? This is where the TABLE data type comes in handy.