November 18, 2009 at 12:48 am
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
November 18, 2009 at 1:02 am
Syntactical!
if @TransCode='TR001'
BEGIN
SET @p1 = dbo.AddFltAmount(@p1,@Flt_Amount) --here where i am getting error
END
....and dont put end if!
---------------------------------------------------------------------------------
November 18, 2009 at 1:03 am
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
November 18, 2009 at 1:07 am
Ohh by the way, why are you using cursor here? Are you trying to create running total?
---------------------------------------------------------------------------------
November 18, 2009 at 1:18 am
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
November 18, 2009 at 1:30 am
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?
---------------------------------------------------------------------------------
November 18, 2009 at 2:42 am
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
November 18, 2009 at 2:45 am
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
November 18, 2009 at 2:53 am
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
November 18, 2009 at 3:00 am
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
March 31, 2011 at 1:47 pm
You probably could run something like:
SELECT SUM(
CASE
WHEN Tbl_Transaction.int_TransCode = 'TR001' THEN Tbl_Transaction.Flt_Amount
WHEN Tbl_Transaction.int_TransCode = 'TR002' THEN Tbl_Transaction.Flt_Amount
(..etc)
WHEN Tbl_Transaction.int_TransCode = 'TR008' AND (some other conditions you need) THEN 0
ELSE 0
END
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)
You don't need to use cursor here.
March 31, 2011 at 1:48 pm
Sorry, I forgot to close brackets:
You probably could run something like:
SELECT SUM(
CASE
WHEN Tbl_Transaction.int_TransCode = 'TR001' THEN Tbl_Transaction.Flt_Amount
WHEN Tbl_Transaction.int_TransCode = 'TR002' THEN Tbl_Transaction.Flt_Amount
(..etc)
WHEN Tbl_Transaction.int_TransCode = 'TR008' AND (some other conditions you need) THEN 0
ELSE 0
END) AS TotalAmount
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)
March 31, 2011 at 1:58 pm
I am not exactly sure what you do in your code, but if you want to pull results as
TR001 555.55
TR002 123.55
TR003 34.00
..
TR008 23.76
THEN use
SELECT Tbl_Transaction.int_TransCode, SUM(Flt_Amount) AS 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)
GROUP BY Tbl_Transaction.int_TransCode
If you show it as
TR001 TR002 TR003 ..... TR008
555.55 123.55 34.00 ..... 23.76
use this
SELECT SUM(CASE WHEN Tbl_Transaction.int_TransCode = 'TR001' THEN Tbl_Transaction.Flt_Amount) AS TR001_Amount,
SUM(CASE WHEN Tbl_Transaction.int_TransCode = 'TR002' THEN Tbl_Transaction.Flt_Amount) AS TR002_Amount,
(..etc)
SUm(CASE WHEN Tbl_Transaction.int_TransCode = 'TR008' AND (some other conditions you need) THEN THEN Tbl_Transaction.Flt_Amount) AS TR008_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)
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply