Can replace the cursor?

Question

5.00/5 (1 vote)

See more:

I written a funtion for personal income tax:

SQL

CREATE Function [dbo].[PersonalIncomeTax](@BasicSalary MONEY, @NumberOfDependents int)
RETURNS MONEY
BEGIN
DECLARE @Taxes MONEY, @ExemptionCircumstances MONEY,@ExemptionItself MONEY,@ExemptionMax MONEY
DECLARE @FromLevel MONEY, @ToLevel MONEY, @PercentTax FLOAT
SET @Taxes=0

SELECT @ExemptionCircumstances = ExemptionCircumstances*@NumberOfDependents,
@ExemptionItself = ExemptionItself,@ExemptionMax = ExemptionMax FROM CF_Thue 

IF(@ExemptionCircumstances>@ExemptionMax)
	SET @BasicSalary=@BasicSalary-@ExemptionMax
ELSE
   SET  @BasicSalary=@BasicSalary-@ExemptionCircumstances
  
DECLARE Taxes_Cursor CURSOR FOR 
	SELECT FromLevel,ToLevel,PercentTax FROM CF_Tax ORDER BY PercentTax
	OPEN Taxes_Cursor

FETCH NEXT FROM Taxes_Cursor INTO @FromLevel, @ToLevel,@PercentTax
WHILE(@@FETCH_STATUS=0)
BEGIN
		IF((@BasicSalary>=@FromLevel) AND (@BasicSalary<@ToLevel))
			SET @Taxes=@Taxes+(@BasicSalary-@FromLevel)*(@PercentTax/100)
		 IF((@BasicSalary>@FromLevel) AND (@BasicSalary>=@ToLevel))
		 	SET @Taxes=@Taxes+(@ToLevel-@FromLevel)*(@PercentTax/100)
	FETCH NEXT FROM Taxes_Cursor INTO @FromLevel, @ToLevel,@PercentTax
END 
CLOSE Taxes_Cursor
DEALLOCATE Taxes_Cursor


Return @Taxes

END

Use the cursor was slow. How can replace the cursor in the code which results not change.Thanks all

Posted 14-Jun-12 22:25pm

violetqa

Updated 14-Jun-12 22:32pm

v3

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

db7uk · Answer 1 · 2012-06-15T00:01:00

You don't need a cursor for this. Can you not do something like:

SQL

SELECT @Taxes = SUM(ISNULL(Tax, 0))
FROM
(
	SELECT CASE WHEN ((@BasicSalary >= FromLevel) AND (@BasicSalary < ToLevel)) THEN ((@BasicSalary - FromLevel)*(PercentTax/100))
				WHEN ((@BasicSalary > FromLevel) AND (@BasicSalary >= ToLevel)) THEN ((ToLevel - FromLevel)*(PercentTax/100))
			ELSE 0
			END AS Tax
	FROM CF_Tax WITH (NOLOCK)
)

Added to that, you can / should look at query performance and cursor performance. Your cursor is very basic and so you could apply the some of the following to the cursor to speed it up.

SQL

DECLARE Taxes_Cursor CURSOR FAST_FORWARD FORWARD_ONLY READ_ONLY FOR  
SELECT FromLevel,ToLevel,PercentTax FROM CF_Tax ORDER BY PercentTax
OPEN Taxes_Cursor

Many many queries can be done using a single select or using a CTE (Common table expression). SQL is fantastic if used correctly.

Prosan · Answer 2 · 2012-06-14T22:42:00

Solution 1

in place of cursor use temprory table or table variable with identity field and iterate through this identity field in place of cursor.
like that

SQL

SELECT FromLevel,ToLevel,PercentTax into #tmpTable FROM CF_Tax ORDER BY PercentTax

alter table #tmpTable add Id int identity
select @cnt=count from #tmpTable 
declare @i int set @i=1
while @i<=@cnt
begin
select @FromLevel=FromLevel, @ToLevel=ToLevel,@PercentTax=PercentTax from #tmpTable where id=@i
--- use your logic
set @i=@i+1
end

Posted 14-Jun-12 22:42pm

Prosan

Updated 14-Jun-12 22:51pm

v2

Comments

violetqa 15-Jun-12 4:46am

Can you say clearly more or write your code change for my code

Prosan 15-Jun-12 4:55am

where u declare cursor in place of use temptable with idendtity field id. and iterate through while loop.

Prosan 15-Jun-12 5:09am

is your problem solved. or i write whole function with tempraroy table. if ur problem solved. mark as solution.