June 23, 2008 at 1:30 am
I have a query that get's the following data:
1v+1
4(v+1)*2
12(v-1)*2
the first column is a decimal number which is the total of the question marks for a part of exam, each row is for a specific part of the exam.
the second column is an equation which has to be applied to the part's total mark.
suppose i have a function dbo.GetMark(@Mark, @Equation) which returns the mark after applying the equation so that a can add a new column to the previous select statement to call the function and return the result of it like this:
SELECT PartMark, Equation, dbo.GetMark(PartMark, Equation) as TotalMark
This seems good but the problem is the function itself.
To evaluate the expression a had to use a dynamic sql like this:
CREATE FUNCTION udfCalcMarkByEquation
(
@mark-3DECIMAL,
@Equation VARCHAR(100)
) RETURNS DECIMAL
AS
BEGIN
DECLARE @sql nvarchar(110)
DECLARE @Result Decimal
SET @sql = 'SELECT @Result = '
SET @sql = @sql + Replace(@Equation, 'v', @mark-3)
EXEC sp_ExecuteSql@statement = @sql,
@param = N'@Result DECIMAL OUTPUT',
@Result = @Result OUTPUT
If @Result IS NULL
SET @Result = 0
RETURN @Result
END
the problem is that we can't call sp_ExecuteSql within a function.
i converted the function to a stored procedure like this:
CREATE Procedure spCalcMarkByEquation
@mark-3DECIMAL,
@Equation VARCHAR(100)
AS
BEGIN
--The Same code here
END
but the problem was that we can't call a procedure in the SELECT statement as we do with functions.
So now the only solution i have is to store the select result in a temp table, and use cursors to move row by row and call the procedure for each row.
But i'm trying to avoid using cursors any more, so does anyone has a better solution for this?
June 23, 2008 at 10:26 am
It depends on how many different possible equations there are. If there's only a small number and they aren't going to change, you could use case:
select
partmark,
equation,
case
when equation = 'v+1' then partmark + 1
when equation = '(v+1)*2' then (partmark + 1)*2
when equation = '(v-1)*2' then (partmark -1)*2
end as result
You could even generate the sql to create the case statement:
select
'when equation = ''' + equation + ''' then ' + REPLACE(equation, 'v', 'partmark')
from #test
group by equation
You could also do it by creating a well indexed hash table of all possible combinations of PartMark and Equation with the calculated result and then join it to the original table in the select statement.
June 23, 2008 at 11:13 am
really the equations are not pre-determined, and they're written by the customer for each record.
So i can't use CASE expression.
Thank you anyway for your participation.
June 23, 2008 at 11:20 am
You'll probably want to explore a CLR function to do this. It's not going to be any kind of speed demon, but it can access things like EVAL. Of course this will turn the assembly into something UNSAFE as I recall, so you're going to want to tread carefully.
On a row by row basis - you can use dynamic SQL to emulate an EVAL. Again - not likely to yield any type of performance, and could possibly be used to inject various things, so again - tread carefully.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply