Geek asks and answers

Sql scalar functions

I have a scalar function in my code that calls another scalar function that calls two other tables, as described below. I know it must be like a pig. It is used throughout the database ... My problem is that it is slightly different from development skills in order to rewrite this as a table-aware function.

I'm trying to get some developers to rewrite the function, but we only have JAVA guys and there is no special SQL developer, so they don’t see any problems. can anyone suggest how this should be rewritten? many thanks...

CREATE FUNCTION [dbo].[getInvertCurrencyExchangeRateByDate](@casino_id char(16),@currency_code char(3), @end_date datetime)
RETURNS float AS
BEGIN

declare @retval float;

set @retval = 
dbo.getCurrencyExchangeRateByDate(@casino_id,@currency_code,@end_date);

if (@retval != 0) return 1/@retval;
return 0;
END

CREATE FUNCTION [dbo].[getCurrencyExchangeRateByDate](@casino_id char(16),@currency_code char(3), @end_date datetime)
RETURNS float AS
BEGIN

declare @retval float;
declare @casino_curr_code char(3);

set @casino_curr_code = 
(SELECT TOP 1 currency_code
FROM Casino
WHERE
casino_id=@casino_id 
);

if (@currency_code = @casino_curr_code) return 1;

set @retval = 
COALESCE(
(
SELECT TOP 1 exchange_rate
FROM CurrencyExchangeRateHistory
WHERE 
casino_id=@casino_id and 
currency_code=@currency_code AND 
transact_time <= @end_date
ORDER BY 
transact_time DESC
),0.0);

return @retval
END
+3
source share
1 answer

, - , .

CREATE FUNCTION dbo.TVF(@casino_id char(16),@currency_code char(3), @end_date datetime)
RETURNS TABLE
AS
RETURN                          --IF THE JOIN FAILS OR RETURNS 0, DIVISION WILL NEVER HAPPEN AND FALL IN THE ISNULL
    SELECT TOP 1 CASE WHEN A.currency_code = @currency_code THEN 1 ELSE ISNULL(1/NULLIF(B.exchange_rate,0), 0) END AS RETVAL
    FROM Casino A
    LEFT JOIN CurrencyExchangeRateHistory B ON A.casino_id = B.casino_id AND B.transact_time <= @end_date AND B.currency_code = A.currency_code
    WHERE A.casino_id = @casino_id
    ORDER BY B.transact_time DESC
+2

More articles:


All Articles