Problem with a scalar function

Posted on 2008-11-19
Last Modified: 2012-05-05
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:

      -- Add the parameters for the function here
      @check bit,
      @TrueVal sql_variant,
      @FalseVal sql_variant
RETURNS sql_variant
      -- 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


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.
Question by:AndyAinscow
    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    what about:
    SELECT      dbo.IIF( (dbo.Fibu.[Beleg ID]<600) , "Lower", "Upper") AS Expr1 FROM.....

    Open in new window

    LVL 142

    Assisted Solution

    by:Guy Hengel [angelIII / a3]
    >1) Can I add a scalar function to the database and then call it inside a view?

    the "problem" is that the expression is not necessarily considered "boolean"
    LVL 44

    Accepted Solution

    Hello AndyAinscow,

    Let's take 2) first, the problem here is that you are using " rather than ' around Lower and Upper so as you do not presumably have quoted_identifier set it will be looking for these as column names rather than string literals.

    Now to the more complicated one, basically what you are trying to achieve is extremely difficult if not impossible. Sql doesn't evaluate an expression as such and return the boolean result. This means that you would either have to pass the expression as a string and then use dynamic sql to evaluate it (could be tricky when you are also passing a column value in!)

    All in all it is probably best to simply convert your IIF() functions directly to case statements within the view rather than trying to replicate the IIF() function with a UDF.


    LVL 43

    Author Comment

    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?
    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    dbo.IIF( CASE WHEN dbo.FIBU.[Beleg ID]<600 THEN 1 ELSE 0 END, 'Lower', 'Upper') AS Expr1

    Open in new window

    LVL 43

    Author Comment

    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!
    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    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.

    Featured Post

    What Security Threats Are You Missing?

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
    International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now