January 4, 2011 at 12:35 pm
Let's see if I can make a reasonable attempt at explaining my dilemma. I've got this dreadful table that I've inherited from an old transaction processing system. The table contains an interest rate index (that is then used to determine interest rates on loan and deposit products) that I need to query in a variety of reports.
The problem is that the table maintains a history of rate changes on a single row. There is a column on the table (rate_index) that tells you how many columns over the most recent rate data can be found (and the date the rate was set). There are 100 possible columns where this data can live. For example:
select rate_name, rate_index, rate_date_1, rate_date_2, rate_date_n, rate_date_100, rate_1, rate_2, rate_n, rate_100
[/font]
Everytime base interest rates change, the most recent value is updated "one column over". So... if you look to the rate_index, you can tell which column you should be looking in to find the data you want.
My plan was to create a function that returns a table, containing a simple dynamic sql statement to go get the most recent data... easy... Nope, I keep banking my head on the ever popular "Only functions and extended stored procedures can be executed from within a function" that appears in many threads in this forum, but so far I haven't seen a usable work around.
The function is simple enough:
create function fn_getRateIndexValue(@IndexKey integer)
RETURNS @RateIndex TABLE
(
index_key integer,
index_date datetime,
rate decimal(8,3)
)
AS
BEGIN
DECLARE @ricnt as varchar(5), @ridate datetime, @rirate decimal(8,3);
DECLARE @SqlStmt nvarchar(4000), @ParmDef nvarchar(1000);
SELECT @ricnt = RICNT from RIRCD where RI_ = @IndexKey
SET @SQLStmt =
'select RIDate_' + @ricnt + ', rirate_' + @ricnt + '/100.00 from rircd where ri_ = ' + Convert(varchar(3), @IndexKey)
SET @ParmDef = N'@_ridate datetime, @_rirate decimal(18,3)'
EXEC sp_executesql @SqlStmt, @ParmDef, @_ridate = @ridate, @_rirate = @rirate
INSERT INTO @RateIndex(index_key, index_date, rate) VALUES (@IndexKey, @ridate, @rirate)
return
END
GO
I've tried using OPENQUERY (but get into trouble because of the parameter/dynamic nature of what I'm trying to do, a straight EXEC(@SQLStmt), and this sp_ExecuteSQL, but the server just won't let me do a lookup to fill that silly table.
My goal is to be able to provide a simple way to fetch the most current interest rate base from a SQL Command for reporting purposes...sadly, I don't control the schema, so the data is where it is...
I can't use a procedure because I need to return two values (the date and the rate) for each possible rate index row that is returned
Goodness this sure seems to be the long way around the barn for an otherwise simple problem.
Any ideas?
January 4, 2011 at 12:43 pm
the short answer is because functions in SQL are different than functions in a programming language...they pretty much have to return data, and cannot change/update data.
because dynamic SQL could potentially build a statement which is side-affecting...that is...could update or delete something...dynamic SQl is not allowed in a function.
It's likely that instead of using dynamic SQL, you could use some CTE's to gather all your possible values instead, and output what you need from them that way instead.
the dynamic SQL seems to be getting data from a table like ri_1 or ri_2 or something...
how many of those parallel tables exist?
Lowell
January 4, 2011 at 12:58 pm
Why can't you use a procedure that returns a resultset?
(Untested code)
CREATE PROCEDURE getRateIndexValue(@IndexKey integer)
AS
BEGIN
DECLARE @ricnt as varchar(5);
DECLARE @SqlStmt nvarchar(4000);
SELECT @ricnt = RICNT from RIRCD where RI_ = @IndexKey
SET @SQLStmt =
'select RIDate_' + @ricnt + ', rirate_' + @ricnt + '/100.00 from rircd where ri_ = ' + Convert(varchar(3), @IndexKey)
EXEC sp_executesql @SqlStmt
END
GO
Call it as such:
CREATE TABLE #RateIndex
(
index_key integer,
index_date datetime,
rate decimal(8,3)
)
INSERT INTO #RateIndex
EXEC getRateIndexValue @SomeParamValue
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 4, 2011 at 1:11 pm
I thought about using a temp table as a solution, however, accessing this data will be a fairly common task for a bank of "average" report writers; teaching them to add that block of code to the top of their reports that need the base index is probably pushing the envelope too far.
If I were to go that route, I could just as easily create a permanent table and fill it with the same technique daily when the warehouse fills; I just hate adding a bunch of daily calcs after the warehouse fill that have to be maintained, but maybe that's the only way to deal with this antique data structure... 🙁
Thanks for the detailed responses.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply