sql scalar functions

Question

I have a scalar function in my code that calls another scalar function that calls 2 other tables as detailed below. I know this must be performing like a pig. It is used throughout the database... My problem is its a little outside developing skills to rewrite this as an table valued function.

I'm attempting to win some of the developers over to rewriting the function, but we only have JAVA guys and no dedicated SQL developer, so they dont 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

Thiago Dantas · Accepted Answer · 2011-08-03 21:48:31Z

I'm sorry, but thats a heck lot of code for something rather simple I think this satisfies the query needs.

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

Collectives™ on Stack Overflow

sql scalar functions

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related