Wednesday, March 21, 2012

Is it possible to create an IIF function for SQL Server?

Hello!

I tried the following code:

create function dbo.iif
(
@.Expression bit,
@.TruePart sql_variant,
@.FalsePart sql_variant
)
returns sql_variant
as
begin
declare @.ReturnValue sql_variant

if @.Expression=1
begin
set @.ReturnValue=@.TruePart
end
else
begin
set @.ReturnValue=@.FalsePart
end

return @.ReturnValue
end

It works fine with statements like this:
select dbo.iif(1,'True','False')

However, when trying a "real" expression, an error appears:
select dbo.iif((1=0),'True','False')
Line 1: Incorrect syntax near '='.

How can I work around this?

Thank you very much in advance.define a variant, and set the value to be the expression. Use the variant in your iif function instead.|||would CASE serve the purpose?

No comments:

Post a Comment