Help on Cursor - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Help on Cursor

Author

Topic

trimavis
Starting Member

1 Post

Posted - 2006-04-20 : 20:55:48

Hello guys...

Maybe you can help me on my dilemma in using this cursor that I created.. It takes 47 - 60 seconds to complete the process.. I have included the source here:

******************************************************************************

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

--drop procedure QueryAffectedInvoices 'PTG0600076',8
ALTER procedure QueryAffectedInvoices @InvoiceNumber varchar(50), @CustID bigint as
begin
--use NIMPEX
SET NOCOUNT ON
--declare @InvoiceNumber varchar(50)
--declare @CustID bigint
--set @InvoiceNumber = 'PJG0400137'
--set @CustID = 19
select ic.ICID,ic.ICID ICIDRef,ic.CustID,ic.ItemID,ic.[Effective Date],ic.[Fiscal Year],ic.Updated,ic.Tag, d.pklinvd_ID,
d.pklinvIDRef,d.[PO Number],(select [Part Code]+ ' ('+convert(varchar,d.[Part Number])+')' from item where i_ID=d.[Part Number])PartNumber,
d.Quantity,d.[Total Amount],t.[Invoice Number],t.ETD [Date Prepared],t.[Commercialized Date] into #tempQueryAffected from
[item change] ic inner join
(pkl_inv_details d inner join pkl_inv_table t on d.pklinvIDRef=t.pklinvID) on d.[Part Number]=ic.ItemID
where (ic.[Effective Date] <= t.ETD) and (t.[Invoice Number]=@InvoiceNumber) and (d.Active=1) and (ic.CustID=@CustID) and (d.Tag='check')
if (select count(*) from #tempQueryAffected) = 0
return 0
declare @numrows bigint
declare @counter bigint
declare @QCount bigint
--*************************************
declare @ICID bigint
declare @ICIDRef bigint
declare @CustIDRef bigint
declare @ItemID bigint
declare @EffectiveDate datetime
declare @FiscalYear bigint
declare @Updated bit
declare @Tag varchar(255)
declare @pklinvd_ID bigint
declare @pklinvIDRef bigint
declare @PONumber varchar(50)
declare @PartNumber varchar(50)
declare @Quantity bigint
declare @TotalAmount float
declare @InvoiceRef varchar(50)
declare @DatePrepared datetime
declare @CommercializedDate datetime
declare @IsUpdated bit
declare @DateModified datetime
--*************************************

-- SELECT * FROM [AFFECTED INVOICES]
exec CreateQueryAffectedCursor
select @numrows = count(ICID) from #tempQueryAffected
set @counter = 1
open QueryAffected
fetch first from QueryAffected into @ICID,@ICIDRef,@CustIDRef,@ItemID,@EffectiveDate,@FiscalYear,@Updated,@Tag,
@pklinvd_ID,@pklinvIDRef,@PONumber,@PartNumber,@Quantity,@TotalAmount,
@InvoiceRef,@DatePrepared,@CommercializedDate
--check first the affected invoices table if there exist, use QCount :)
if (select count(pklinvd_id) from [affected invoices] ai where ai.ICIDRef=@ICID and ai.[Invoice Number]=@InvoiceRef) > 0
begin
select @DateModified = [Date Modified] from [Affected Invoices] ai where ai.ICIDRef=@ICID and ai.[Invoice Number]=@InvoiceRef
select @IsUpdated = Updated from [Affected Invoices] ai where ai.ICIDRef=@ICID and ai.[Invoice Number]=@InvoiceRef
insert into [Affected Invoices]
values (@pklinvd_ID,@pklinvIDRef,@InvoiceRef,@PONumber,@PartNumber,@Quantity,@TotalAmount,
@DatePrepared,@ICIDRef,@IsUpdated,@Tag,@DateModified,@CommercializedDate)
end
else
begin
insert into [Affected Invoices]
values (@pklinvd_ID,@pklinvIDRef,@InvoiceRef,@PONumber,@PartNumber,@Quantity,@TotalAmount,
@DatePrepared,@ICIDRef,0,@Tag,NULL,@CommercializedDate)
end
while (@@fetch_status=0) and (@counter<@numrows)
begin
fetch next from QueryAffected into @ICID,@ICIDRef,@CustIDRef,@ItemID,@EffectiveDate,@FiscalYear,@Updated,@Tag,
@pklinvd_ID,@pklinvIDRef,@PONumber,@PartNumber,@Quantity,@TotalAmount,
@InvoiceRef,@DatePrepared,@CommercializedDate
--check first the affected invoices table if there exist, use QCount :)
if (select count(pklinvd_id) from [affected invoices] ai where ai.ICIDRef=@ICID and ai.[Invoice Number]=@InvoiceRef) > 0
begin
select @DateModified = [Date Modified] from [Affected Invoices] ai where ai.ICIDRef=@ICID and ai.[Invoice Number]=@InvoiceRef
select @IsUpdated = Updated from [Affected Invoices] ai where ai.ICIDRef=@ICID and ai.[Invoice Number]=@InvoiceRef
insert into [Affected Invoices]
values (@pklinvd_ID,@pklinvIDRef,@InvoiceRef,@PONumber,@PartNumber,@Quantity,@TotalAmount,
@DatePrepared,@ICIDRef,@IsUpdated,@Tag,@DateModified,@CommercializedDate)
end
else
begin
insert into [Affected Invoices]
values (@pklinvd_ID,@pklinvIDRef,@InvoiceRef,@PONumber,@PartNumber,@Quantity,@TotalAmount,
@DatePrepared,@ICIDRef,0,@Tag,NULL,@CommercializedDate)
end
set @counter = @counter+1
end
drop table #tempQueryAffected
close QueryAffected
deallocate QueryAffected
end

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

******************************************************************************
hope someone could help... i'm also trying to figure this out without using any cursor...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-20 : 21:05:11

Don't use cursor. Rewrite your query using set based operation

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-21 : 02:15:58

Tell us what you are trying to do. If you want to copy data from one table to other, then

Insert into table1(columns)
select columns from table2

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources