AndyAinscow
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.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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?
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
ASKER
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!
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.
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.
Open in new window