Link to home
Start Free TrialLog in
Avatar of AndyAinscow
AndyAinscowFlag for Switzerland

asked on

Problem with a scalar function

I am attempting to provide a custom function that should replace provide an IIF function (Access SQL) in an upsized database.

I have defined a new scalar function as follows:

ALTER FUNCTION [dbo].[IIF]
(
      -- Add the parameters for the function here
      @check bit,
      @TrueVal sql_variant,
      @FalseVal sql_variant
)
RETURNS sql_variant
AS
BEGIN
      -- Declare the return variable here
      DECLARE @Result sql_variant

      -- Add the T-SQL statements to compute the return value here
      SET @Result = CASE WHEN @check<>0 THEN @TrueVal ELSE @FalseVal END
      -- Return the result of the function
      RETURN @Result

END

I want the IIF function to take 3 parameters, a boolean value, a value for the true result and a value for the false result.  (the results can be strings, numbers... so I have declared them as variants and require the function to return a variant).

The function compiles (Verify SQL Syntax) without problem.

In a view I have the following:
SELECT      dbo.IIF(dbo.Fibu.[Beleg ID]<600, "Lower", "Upper") AS Expr1 FROM.....

If I then use the 'Verify SQL Syntax' option I get an error - Incorrect syntax near '<'


Just for experimentation I then modified that to
SELECT      dbo.IIF(dbo.Fibu.[Beleg ID], "Lower", "Upper") AS Expr1 FROM.....
and now get the following errors:- Invalid Column Name 'Lower', Invalid Column Name 'Upper'


Obviously I have a serious misunderstanding.
1) Can I add a scalar function to the database and then call it inside a view?
2) Assuming the answer to 1) is yes then what is wrong with my attempt above.
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

what about:
SELECT      dbo.IIF( (dbo.Fibu.[Beleg ID]<600) , "Lower", "Upper") AS Expr1 FROM.....

Open in new window

SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of AndyAinscow

ASKER

OK, some progress.

I have changed the statement to
 dbo.IIF(dbo.FIBU.[Beleg ID], 'Lower', 'Upper') AS Expr1

This compiles correctly and does 'run' (but not exactly what I desire as a result).


Changing to
 dbo.IIF((dbo.FIBU.[Beleg ID]<600), 'Lower', 'Upper') AS Expr1
still results in the error Incorrect Syntax near '<'



>>All in all it is probably best to simply convert your IIF() functions directly to case statements within the view
Er, yes but.  I am upsizing an existing database.  There are many rather complex queries that use an IIF statement.  The first (alphabetically) actually has 5 different IIF statements so I hoped I could take a simpler approach by supplying my own function to directly replace the IIF function.


Any further ideas?
yes:

 
dbo.IIF( CASE WHEN dbo.FIBU.[Beleg ID]<600 THEN 1 ELSE 0 END, 'Lower', 'Upper') AS Expr1

Open in new window

OK, I asked for that.
That looks as if it should work, however it rather defeats the purpose of have the custom function doesn't it.


I suspect this isn't going to go anywhere.  :-(
I think I have a few hours of mind numbing drudgery ahead of me, aaargh!
I see what you mean :)
and now, I remember I had more or less the same dilemma, and could not find any solution (in sql 2000-2005). I did not yet check in sql 2008, though.