SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Using Cursors Inside Stored procedure problem


Using Cursors Inside Stored procedure problem

Author
Message
chandrashekar.2512
chandrashekar.2512
Old Hand
Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)

Group: General Forum Members
Points: 321 Visits: 55
Dear all,
I would like to create cursor inside the stored procedure which needs to fetch each record and do some calculation, this calculation depends on the other field int table. So based on the condition i need to return the value.
But when i try to do the calculation it gives
Line 21: Incorrect syntax near '@p1'.
Please help me as this is very high priority work for me.

Here is the stored procedure how it goes
Create Procedure DummyReport
(@txt_StateCode int,
@txt_DistrictCode int,
@txt_Date2 smalldatetime,
@txt_AccountNo varchar(20),
@txt_DPCode int
)

As
DECLARE @TransCode varchar(20)
DECLARE @Flt_Amount int
DECLARE @p1 int

DECLARE Cur_Flt_Amt Cursor FAST_FORWARD FOR
SELECT Tbl_Transaction.int_TransCode,Tbl_Transaction.Flt_Amount FROM Tbl_Transaction WHERE txt_StateCode=@txt_StateCode and txt_DistrictCode=@txt_DistrictCode AND dat_Date <= CONVERT(DATETIME, @txt_Date2 , 102) AND (txt_AccountNO = @txt_AccountNo ) and (int_DPCode=@txt_DPCode)
OPEN Cur_Flt_Amt
FETCH NEXT FROM Cur_Flt_Amt INTO @TransCode, @Flt_Amount
WHILE @@FETCH_STATUS = 0

if @TransCode='TR001'
@p1 = dbo.AddFltAmount(@p1,@Flt_Amount) --here where i am getting error
end if

CLOSE Cur_Flt_Amt
DEALLOCATE Cur_Flt_Amt
return @p1

create function AddFltAmount
(@p1 int,
@fltAmt int)
Returns int
As
Begin
Return @p1+ @fltAmt
end

Thanks in advance
Chandrashekar
Nabha
Nabha
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3577 Visits: 1815
Syntactical!


if @TransCode='TR001'
BEGIN
SET @p1 = dbo.AddFltAmount(@p1,@Flt_Amount) --here where i am getting error
END



....and dont put end if!

---------------------------------------------------------------------------------
Ian Scarlett
Ian Scarlett
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10599 Visits: 7233
You're thinking in Visual Basic (or similar) not TSQL... :-)

if @TransCode='TR001'
SET @p1 = dbo.AddFltAmount(@p1,@Flt_Amount) --here where i am getting error
--this would also cause an error end if



Nabha
Nabha
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3577 Visits: 1815
Ohh by the way, why are you using cursor here? Are you trying to create running total?

---------------------------------------------------------------------------------
chandrashekar.2512
chandrashekar.2512
Old Hand
Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)

Group: General Forum Members
Points: 321 Visits: 55
Thanks for the reply,

Yes i am taking the sum of flt_amount field based on the transcode in the int_transcode
if the Transcode='TR001' then i need to add float_amount,
it goes upto Transcode='TR008' like eight conditions and i want to return eight out put from the procedure, i dont know how to do it please help me guys.

Thanks
Nabha
Nabha
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3577 Visits: 1815
With no test data and create table script, cant help much but dont you think you need 'Group By' clause rather than this cursor. I dont think you need a running total but a sum of flat_amount for a transaction TR001 and several other things. It means for each group you need the 'sum' of the flat_amount which warrants for a group by clause. No?

---------------------------------------------------------------------------------
vihi.koyott
vihi.koyott
SSC-Enthusiastic
SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)

Group: General Forum Members
Points: 183 Visits: 83
You are not moving the cursor ahead.

WHILE (@@FETCH_STATUS=0)
BEGIN
if @TransCode='TR001'
BEGIN
SET @p1 = dbo.AddFltAmount(@p1,@Flt_Amount) --here where i am getting error
END
FETCH NEXT FROM Cur_Flt_Amt INTO @TransCode, @Flt_Amount
END
chandrashekar.2512
chandrashekar.2512
Old Hand
Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)

Group: General Forum Members
Points: 321 Visits: 55
You are right, even then it is not faster.

Let me tell you the scenario,
I have to generate a report which is having lakhs of records and need to dispaly it. i am fetching the records and doing adding it in my asp file itself.

SQLStr1="Execute Report3_tbl_Trans_dummy "& txt_StateCode &","& txt_DistrictCode &",'"&txt_Date2 &"','"& txt_AccountNo &"','"& txt_DPCode &"'"
response.write"sql --5" &SQLStr1 &"
"
'response.end

adoRs1.ActiveConnection = DBCon
adoRs1.Source = SQLStr1
adoRs1.CursorLocation = 3 'adUseClient
adoRs1.CursorType = 0 'adOpenForwardOnly
adoRs1.Locktype = 1 'adLockReadOnly
adoRs1.Open
response.write "recordCount for Step2_5-->"& adoRs1.RecordCount &"
"
Set adoRs1.ActiveConnection = Nothing
FOR S=1 TO adoRs1.RecordCount

IF UCASE(TRIM(adoRs1("int_TransCode")))="TR001" THEN
TR1=CDBL(TR1)+CDBL("0"& adoRs1("flt_Amount"))
ELSEIF UCASE(TRIM(adoRs1("int_TransCode")))="TR002" THEN
TR2=CDBL(TR2)+CDBL("0"& adoRs1("flt_Amount"))
ELSEIF UCASE(TRIM(adoRs1("int_TransCode")))="TR003" THEN
TR3=CDBL(TR3)+CDBL("0"& adoRs1("flt_Amount"))
ELSEIF UCASE(TRIM(adoRs1("int_TransCode")))="TR004" THEN
TR4=CDBL(TR4)+CDBL("0"& adoRs1("flt_Amount"))
ELSEIF UCASE(TRIM(adoRs1("int_TransCode")))="TR005" THEN
TR5=CDBL(TR5)+CDBL("0"& adoRs1("flt_Amount"))
ELSEIF UCASE(TRIM(adoRs1("int_TransCode")))="TR006" THEN
TR6=CDBL(TR6)+CDBL("0"& adoRs1("flt_Amount"))
ELSEIF UCASE(TRIM(adoRs1("int_TransCode")))="TR007" THEN
TR7=CDBL(TR7)+CDBL("0"& adoRs1("flt_Amount"))
ELSEIF UCASE(TRIM(adoRs1("int_TransCode")))="TR000" THEN
TR8=CDBL(TR8)+CDBL("0"& adoRs1("flt_Amount"))
END IF
adoRs1.MoveNext
NEXT
adoRs1.close

adoRs.MoveNext
NEXT

this is my code i am thinking that, since i am doing this calculation in asp file is that going to decrease my performance. is that, than how can i do this calculation in database itself.
What are the steps i have to do, Either i have to write stored procedure or
how can improve my sql performance by doing this
chandrashekar.2512
chandrashekar.2512
Old Hand
Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)

Group: General Forum Members
Points: 321 Visits: 55
Thanks for the reply,
When i run that procedure, i am getting error like below

Server: Msg 232, Level 16, State 3, Procedure DummyReport, Line 26
Arithmetic overflow error for type int, value = 2561015000.000000.
The 'DummyReport' procedure attempted to return a status of NULL, which is not allowed. A status of 0 will be returned instead.

Please some some body tell me that is that procedure is correct or not, if it is wrong what is the correct procedure statement for that

Thanks
Chandrashekar
vihi.koyott
vihi.koyott
SSC-Enthusiastic
SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)

Group: General Forum Members
Points: 183 Visits: 83
Hi,

1. In your procedure @p1 is not assigned any value so no matter what function you call,it'll always return a null...
(set @p1 = dbo.AddFltAmount(@p1,@Flt_Amount))
2.There is a better way than using a cursor.Use group by.
3.Pls check the function AddFltAmount ,you are not adding anything.
Please provide the details about the function then i may be able to help you out with the group by satement.

Regards
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search