Can a CLR Function be made similar to Coalesce?

Question

Can a CLR Function be made similar to Coalesce?

Bradley Deem

SSCrazy

Points: 2565
More actions
November 22, 2008 at 4:41 pm

#201658

I'd like to make a function that works like coalesce in that it accepts an unlimited number of parameters of a single type and outputs an answer. In particular I would like to create a function that can determine the minimum from multiple columns. In the end I would like to use this function to make a computed column.
Yes, I realize that might be a sign of denormalized data structure, which in the scenario I'm apply this to is denormalized.
Let's do an example
Columns
OptOutDate datetime
InactiveDate datetime
UnsubscribeDate datetime
The UnsubscribeDate is a computed column that is the minimum of the two columns OptOutDate and InactiveDate.
Thanks in advance.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

In 2005, the options are a comma-delinited list or an xml document or similar. Messy, I know. In 2008 you can use a table-valued parameter.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Piotr.Rodak SSCrazy Eights Points: 9376 More actions · Answer 2

Hi,

Why don't you use CASE statement? Maybe more explicit, but deffinitely faster.

create table t1

(

OptOutDate datetime,

InactiveDate datetime,

UnsubscribeDate as case when OptOutDate < InactiveDate then OptOutDate else InactiveDate end

)

go

insert into t1 (OptOutDate, InactiveDate) values (getdate(), dateadd(day, 1, getdate()))

insert into t1 (OptOutDate, InactiveDate) values (getdate(), dateadd(day, -1, getdate()))

select * from t1

go

drop table t1

Regards

Piotr

...and your only reply is slàinte mhath

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 3

The limitation isn't a CLR limitation, it is a limitation in SQL in general. A function has a set of input parameters, and a set output payload, even if it is a TVF, you still have a set schema that it is going to output. I wouldn't even recommend CLR for what you are trying. A simple CASE statement for the computed column as Piotr demonstrated will do the job in TSQL natively just fine.

RBarryYoung SSC Guru Points: 143329 More actions · Answer 4

Well you could write something like this, or its equivalent in CLR:

Create function dbo.fnMAX(

@p1 SQL_Variant

, @p2 SQL_Variant = NULL

, @p3 SQL_Variant = NULL

) Returns SQL_Variant

AS

Begin

Declare @val SQL_Variant

Set @val = @p1

IF @p2 IS NOT NULL

IF @p2 > @val Set @val = @p2

IF @p3 IS NOT NULL

IF @p3 > @val Set @val = @p3

Return @val

End

But it will never work like Coalesce. For one thing, User-defined functions (unlike built-in ones) cannot be invoked without specifying all of the parameters. You have to explicitly pass NULL for the ones that you want to skip.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Bradley Deem SSCrazy Points: 2565 More actions · Answer 5

Thank you all for your input. I did implement it as a CASE statement originally (see below). I am looking for a non case statement solution to handle the problem where many columns are used. Since as the number of columns increase the complexity of the case statement gets unwieldy.

case

when OptOutDate IS NOT NULL AND InactiveDate IS NOT NULL then

case when OptOutDate<InactiveDate then OptOutDate

else InactiveDate end

when OptOutDate IS NULL AND InactiveDate IS NULL then NULL

when OptOutDate IS NOT NULL then OptOutDate

when InActiveDate IS NOT NULL then InactiveDate

end

Perhaps using ISNULL() could reduce this complexity.

RBarryYoung SSC Guru Points: 143329 More actions · Answer 6

What I laid out for you is how to do it.

If the optional parameters handling is a problem, just implement your case function as a two-parameter version that has null-handling and then use nesting when you invoke it:

, dbo.fnMAX(p1, dbo.fnMAX(p2, dbo.fnMAX(p3, p4))) as [MaxValue]

, ...

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Bradley Deem SSCrazy Points: 2565 More actions · Answer 7

Thanks Barry, I'll have to do some performance testing and see how goes if I ever need to apply this across multiple columns.