I ran across something a couple years back and I just ran across this sweetness again. I had a database populated by a VBScript that inventoried specific things on our servers with a web front end. I wanted to make it easy for users to see if a server was still under warranty at a glance and not have to do the “datediff” in their head. My table had a column that contained the warranty expiration date so I could have written this logic into the web page, but it would have to process it for every page load. By doing this in SQL the computation only has to be done once. The problem is that I wanted a string returned based on a date calculation and not just the result of some calculation on a column or two. I discovered a cool way to do this was to write a user defined function because you can use UDFs in your computed column definitions. Here is the UDF I created:
USE [MyDB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create function [dbo].[udf_warrantystatusupdate]
(
@expdate datetime
)
returns char(8)
as
begin
declare @status char(8)
set @status = case
when @expdate > getdate() then ‘Active’
when @expdate < getdate() then ‘Expired’
else null
end
return(@status)
end
Here is my column definition in my table:
[WarrantyStatus] AS ([dbo].[udf_warrantystatusupdate]([warrantyexpdate]))