cursor

hi

i m using cursor as per my need in my procedure.....below is my code.....can any body tell me cursor is good way to use or there is another way to write my procedure

[code]

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

-- delete from Tbl_DC_UploadMaster
-- exec Usp_Redem_Funding
-- select * from tbl_account_funding order by account_funding_id desc
-- Select * from Tbl_DC2488_funding order by DC2488_ID DESC
-- SELECT * FROM Tbl_DirectCredit_AC WHERE TRADE_DATE='2007-04-11 00:00:00.000' ORDER BY DC_ID DESC
-- Select * from Tbl_PayOrder1310_funding order by PayOrder1310_ID desc
ALTER proc pruamc.Usp_Redem_Funding

AS
BEGIN

SELECT DISTINCT(Redemption_Upload_Master_ID) into #MatchedFile

FROM ((Tbl_Redemption_UploadMaster UM (NOLOCK)

INNER JOIN Tbl_Redemption_UploadDetails UD (NOLOCK)

ON UM.Redemption_Upload_Master_ID = UD.RedemptionUploadMaster_Id)

INNER JOIN Tbl_DC_UploadMaster (NOLOCK)

ON

substring(UM.File_Name,len(UM.File_Name)-9,len(UM.File_Name)) =
substring(Tbl_DC_UploadMaster.File_Name,len(Tbl_DC_UploadMaster.File_Name)-9,len(Tbl_DC_UploadMaster.File_Name)))

INNER JOIN Tbl_Payorder_UploadMaster (NOLOCK)

ON

substring(UM.File_Name,len(UM.File_Name)-9,len(UM.File_Name)) =
substring(Tbl_Payorder_UploadMaster.File_Name,len(Tbl_Payorder_UploadMaster.File_Name)-9,len(Tbl_Payorder_UploadMaster.File_Name))

WHERE (([UD].[compare_status]='Pending' AND UM.Status <> '1' ));

-- select * from #MatchedFile
-- DROP TABLE #MatchedFile
--Cursor Code
declare @.redemid int

--Cursor declaration--
-- drop cursor InsertFunding
declare InsertFunding cursor for
Select Redemption_Upload_Master_ID from #MatchedFile

--Open cursor-
OPEN InsertFunding

-Fetch into variables--
FETCH NEXT From InsertFunding into @.redemid

WHILE @.@.fetch_status = 0
Begin

INSERT into pruamc.tbl_account_funding
(
Redemption_Upload_Master_Id,
scheme_group,
schemegroup_accode,
trade_date,
credit,
dcpayflag

)
SELECT RedemptionUploadMaster_Id,scheme_group,account_redempac,dbo.NewPayOutDate(@.redemid)AS Trade_Date, sum(amount), payout_mechanism FROM pruamc.Tbl_Redemption_UploadDetails
INNER JOIN tbl_accountmaster
ON tbl_accountmaster.account_schemegroup = pruamc.Tbl_Redemption_UploadDetails.scheme_group

WHERE
pruamc.Tbl_Redemption_UploadDetails.RedemptionUploadMaster_Id=@.redemid
GROUP BY scheme_group,payout_mechanism ,RedemptionUploadMaster_Id,trade_date ,account_redempac
ORDER BY scheme_group,payout_mechanism,account_redempac

- Insert into DC2488_funding table

INSERT INTO pruamc.Tbl_DC2488_funding
(
Redemption_Upload_Master_Id,
scheme_group,
schemegroup_accode,
trade_date,
credit,
DC_Flag,
Compare_Status
)
SELECT redemption_upload_master_id,scheme_group,schemegroup_accode,trade_date,credit,dcpayflag,'Pending' as Compare_status
FROM
tbl_account_funding
WHERE
Redemption_Upload_Master_ID=@.redemid AND dcpayflag NOT IN('CHEQUE') AND dcpayflag IS NOT NULL
GROUP BY
redemption_upload_master_id,scheme_group,credit,dcpayflag,trade_date,schemegroup_accode

Update tbl_account_funding set debit = credit where credit in (select credit from Tbl_account_funding td where credit = td.credit)

INSERT INTO pruamc.Tbl_DirectCredit_AC
(
Redemption_Upload_Master_Id,
Scheme_Group,
schemegroup_accode,
trade_date,
Credit,
CustomerName,
Bank_Name,
Bank_AC,
CompareStatus,
Folio_no

)
SELECT
MF.RedemptionUploadMaster_Id,
MF.scheme_group,
tbl_accountmaster.account_redempac,
dbo.NewPayOutDate(@.redemid)AS Trade_Date,
MF.Amount,
MF.Investor_name,
BM.Redem_BankAccountName,
MF.Payee_AC_No,
'Pending' as CompareStatus,
MF.Folio_No
FROM Tbl_Redemption_UploadDetails MF
INNER JOIN tbl_accountmaster
ON tbl_accountmaster.account_schemegroup = scheme_group
INNER JOIN tbl_Redem_AccountMaster BM
ON BM.Redem_DcPayFlag = payout_mechanism

WHERE MF.RedemptionUploadMaster_Id=@.redemid AND payout_mechanism NOT IN('CHEQUE') AND payout_mechanism IS NOT NULL

- Insert into 1310 table

INSERT INTO pruamc.Tbl_PayOrder1310_funding
(
Redemption_Upload_Master_Id,
Scheme_Group,
schemegroup_accode,
trade_date,
Credit,
Folio_No

)
SELECT
MF.RedemptionUploadMaster_Id,
MF.scheme_group,
tbl_accountmaster.account_redempac,
dbo.NewPayOutDate(@.redemid)AS Trade_Date,
MF.Amount,
Folio_No
FROM Tbl_Redemption_UploadDetails MF
INNER JOIN tbl_accountmaster
ON tbl_accountmaster.account_schemegroup = scheme_group

WHERE MF.RedemptionUploadMaster_Id=@.redemid AND payout_mechanism IN('CHEQUE') AND payout_mechanism IS NOT NULL

UPDATE pruamc.Tbl_Redemption_UploadMaster SET Status='1' WHERE Redemption_Upload_Master_Id = @.redemid
FETCH NEXT From InsertFunding INTO @.redemid

END
close InsertFunding
deallocate InsertFunding

End

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

[/code]

try removing the cursor and the loop and put this in each select part of the insert...

instead of RedemptionUploadMaster_id=redemid, put RedemtionUploadMaster_Id in (select Redemption_Upload_Master_ID from #MatchedFile)

This would remove the cursor. The performance may be better or worse depending on a lot of thing (contention on tables, number of rows, ect).

Martin!

|||

hi

thanx for reply..but i will get no of Redemption_Upload_Master_ID ...i think your code is for only a single id....my #MatchedFile

table will contain more than one id..in that case how will i get all those ids.

thanx

|||But when you use IN (select ...) you mean for each id from select|||

u mean this will work like loop itself ..okay let me try it

thanx a lot

|||

okay one more thing ..in bold function also i want to pass id .how i can pass in it?

can u tell me?

[code]

SELECT RedemptionUploadMaster_Id,scheme_group,account_redempac,dbo.NewPayOutDate(1) AS Trade_Date, sum(amount), payout_mechanism
FROM pruamc.Tbl_Redemption_UploadDetails

[/code]

|||

Just use dbo.NewPayOutDate(MF.RedemptionUploadMaster_Id) AS Trade_Date .... WHERE MF.RedemptionUploadMaster_Id IN (....), because on each loop step MF.RedemptionUploadMaster_Id equals to current id from select

sql