Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Using Cursors Inside Stored procedure problem Expand / Collapse
Author
Message
Posted Wednesday, November 18, 2009 12:48 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, March 5, 2010 12:48 AM
Points: 23, 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


Post #820612
Posted Wednesday, November 18, 2009 1:02 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, August 2, 2011 3:36 AM
Points: 579, Visits: 1,803
Syntactical!

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

....and dont put end if!


---------------------------------------------------------------------------------
Post #820613
Posted Wednesday, November 18, 2009 1:03 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, June 16, 2014 7:32 AM
Points: 1,327, Visits: 4,504
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



Post #820615
Posted Wednesday, November 18, 2009 1:07 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, August 2, 2011 3:36 AM
Points: 579, Visits: 1,803
Ohh by the way, why are you using cursor here? Are you trying to create running total?

---------------------------------------------------------------------------------
Post #820618
Posted Wednesday, November 18, 2009 1:18 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, March 5, 2010 12:48 AM
Points: 23, 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
Post #820620
Posted Wednesday, November 18, 2009 1:30 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, August 2, 2011 3:36 AM
Points: 579, Visits: 1,803
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?

---------------------------------------------------------------------------------
Post #820625
Posted Wednesday, November 18, 2009 2:42 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, January 12, 2011 2:18 AM
Points: 35, 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
Post #820644
Posted Wednesday, November 18, 2009 2:45 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, March 5, 2010 12:48 AM
Points: 23, 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
Post #820648
Posted Wednesday, November 18, 2009 2:53 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, March 5, 2010 12:48 AM
Points: 23, 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
Post #820651
Posted Wednesday, November 18, 2009 3:00 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, January 12, 2011 2:18 AM
Points: 35, 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

Post #820656
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse