Create Function for computed column

[sparkyrose]

Hi All,

I'm trying to create a UDF (SQL Server 2000, btw) which will take the date from a column in the same table, add years based on a calculation of two other columns, and arrive at the final date.

If it's helpful to know I'm taking the end of a current lease, adding the renewal options, and arriving at the final lease end date. I need the If...Else part because not all leases have available renewals

This is what I came up with but it gives two error messages, listed below the code.

CREATE FUNCTION LeaseEndFunc 
	(@Currenttermend DateTime,
	 @RenewalPeriods Decimal (9),
	 @RenewPeriodLen Decimal (9))
RETURNS DateTime
AS
BEGIN
IF (SELECT RenewalPeriods FROM dbo.[Main Table]) Is Null
BEGIN 
(SELECT Currenttermend FROM dbo[Main Table])
END
ELSE
RETURN (SELECT dateadd(year,([RenewalPeriods] * [RenewPeriodLen]),[Currenttermend])
FROM dbo.[Main Table])
END

Server: Msg 444, Level 16, State 2, Procedure LeaseEndFunc, Line 10
Select statements included within a function cannot return data to a client.

Server: Msg 455, Level 16, State 1, Procedure LeaseEndFunc, Line 65535
The last statement included within a function must be a return statement.

Any pointers gratefully appreciated!

 

[gmmastros]

There are a couple things I would like to talk about here.

First, you don't really need a function because you should be able to do this without it. But, it's important to understand how to write a scalar function, so i'll start with that.

When you write a function, you need to be a little careful about how you do it because you can really kill your performance. Specifically, if your function uses data from a table, then the function is evaluated for each row in your outer query. For example, you could write the function so that you pass in the LeaseID (or some such thing). The function could get the data from the table and return a result. But, if you do this for hundreds of thousands of rows, each function call will hit the table separately, making the performance a lot worse than it has to be.

In your case, I see that you are passing in 3 parameters to the function, but you're not actually using them. The code you wrote appears to be going to the table to get the data, which is completely unnecessary since you are passing the parameters in. Based on your code, I think the function you are looking for is this:

CREATE FUNCTION LeaseEndFunc 
    (@Currenttermend DateTime,
     @RenewalPeriods Decimal (9),
     @RenewPeriodLen Decimal (9))
RETURNS DateTime
AS
BEGIN
  Declare @Output DateTime

  IF @RenewalPeriods Is NULL
    Set @Output = @CurrentTermEnd
  Else
    Set @Output = DateAdd(year,@RenewalPeriods * @RenewPeriodLen, @CurrentTermEnd)

  Return @Output
END

Notice how I declare a function. Then I use an IF statement to control how the data is set. Most importantly, notice that there are no references to any tables. This is important because it will allow the function to execute faster. For a single row, you would probably never notice, but like I said earlier, for many rows, you will begin to notice performance problems with table access inside the function.

Now let's look at the error messages you were getting and then we will see how the code I suggest handles them:

[tt][!]Server: Msg 444, Level 16, State 2, Procedure LeaseEndFunc, Line 10
Select statements included within a function cannot return data to a client.[/!][/tt]

Functions can only return values that you specify. Since you have select statements in the function, SQL things you want to return the results of the select statement. Notice in my example how I declare an @Output variable, and then use Set @Output = blah.... This is clearly setting the value of a variable and not trying to output a result set.

[tt][!]Server: Msg 455, Level 16, State 1, Procedure LeaseEndFunc, Line 65535
The last statement included within a function must be a return statement.[/!][/tt]

In this case you have "[tt]RETURNS DateTime[/tt]". This means you have a scalar function (one value with data type = DateTime). The last line of a function must return the output using the RETURN keyword. Notice the syntax I use, that is the correct way of returning data from a function.

Lastly, like I mentioned at the beginning, you don't really need to use a function at all because you have all the data you need. This can be accomplished using a case/when statement.

Alter Table [Main Table] 
Add LeaseEnd As Case When RenewalPeriods Is NULL 
                     Then CurrentTermEnd 
                     Else DateAdd(Year, RenewalPeriods * RenewPeriodLen, CurrentTermEnd) 
                     End

Or, since you are using NULL, you could do this:

Alter Table [Main Table] 
Add LeaseEnd As DateAdd(Year, Coalesce(RenewalPeriods * RenewPeriodLen, 0), CurrentTermEnd)

Think of the last code bit as...

Multiply RenewalPeriods * RenewPeriodLen. If either value is NULL, the result would be NULL. We then use the coalesce function to return the value 0. 0 would be added to your date, which wouldn't affect it.

Make sense?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[sparkyrose]

Hi George,

Firstly, thanks for the really thorough response. Lots for me to digest.

You're correct that I don't need a function, and in fact, it's even simpler than that. I realized after reading your post that I could simply take

DateAdd(Year, Coalesce(RenewalPeriods * RenewPeriodLen, 0), CurrentTermEnd)

and put it in the column's formula. Easy! This was my initial approach but the Nulls threw it off. Using Coalesce is perfect!

At least I'll have a good background for when I do actually need a Function.

 

[sparkyrose]

I encountered one problem in that I didn't realize that DateAdd can only handle an integer as the number. I have renewal period of less than a year.

I could break the field into two (years and months) or potentially even three (days too) but this would make the form not very user-friendly. Any alternatives spring to mind?

 

[gmmastros]

Can you show some sample data and expected results. I don't need to see all the data in the table, just the relevant columns and only for several rows.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[sparkyrose]

Here is a sample:

Currenttermend RenewalPeriods RenewPeriodLen Leaseend
31-May-16 0 0 31-May-16
31-Oct-14 1 2.83 31-Oct-16
30-Sep-14 0 0 30-Sep-14
15-Dec-14 1 5 15-Dec-19
22-Dec-29 2 5 22-Dec-39
29-Sep-29 2 5 29-Sep-39

The columns are off but I think you can see what should be where. In the second row, the Leaseend date should be Aug 31, 2017. Unfortunately the DateAdd function cannot handle non-integers (at least according to BOL).

Thanks

 

[gmmastros]

I see what you mean. In your case, I would suggest the you multiply your calculation by 12 and then add months instead of years. I did this and still got the wrong result, but I was able to figure that out too. It appears as though it's a rounding problem. Specifically, when you convert to int, the fractional part is truncated. Ex:

Select 2.83 * 12
Select Convert(Int, 2.83 * 12)

You get 33.96 and 33. In your case, I think you want to use the value 34, so we'll need to use the round function, like this:

DateAdd(Month, Coalesce(Round(RenewalPeriods * RenewPeriodLen * 12, 0), 0), CurrentTermEnd)

Notice that I first multiply the 2 values and then multiply by 12. This effectively converts from years to months. Then I round the number to 0 digits (effectively rounding to a whole number). Then, we SQL Converts this to an INT, there won't be any mistakes. Lastly, notice that I am adding months to the CurrentTermEnd.

If I am not mistaken, I think this is probably the code you're looking for. More importantly, I hope you understand the thought process that led me to this code.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[sparkyrose]

Perfect!

Much better than breaking mo and yr to separate columns as I had thought of.

I think I'm good to go now, thanks for your help!