Alternate for sql cursors

Hi Guys,
I am working on a performance tuning project but was not able to rewrite the below given procedure with out using cursors could any body advise , bcoz this is urgent.
Thanks in advance

CREATE PROCEDURE [dbo].S_LPM_SUPPORT_DATA
AS
BEGIN
DECLARE @promotion_id INT
DECLARE @STATUS VARCHAR(32)
DECLARE @upcadded INT
DECLARE @upcdeleted AS INT
DECLARE @totalupcs as int
DECLARE @TODAY AS SMALLDATETIME
DECLARE @EXPORT_DATE AS SMALLDATETIME
SELECT @TODAY = CONVERT(SMALLDATETIME, CONVERT(VARCHAR(12), GETDATE()), 103)
DELETE T_LPM_SUPPORT_UPC_LIST WHERE PROCESSDATE= @TODAY
DELETE T_LPM_support_upc_count WHERE STATUS = 'EXPIRED YESTERDAY'
declare c1 cursor for
select promotion_id, date_to_export
from t_lpm_promotion (nolock)
WHERE dbo.F_PROMOTION_STATUS(promotion_id, @TODAY) IN ('LIVE', 'PENDING_START')
order by promotion_id
open c1
fetch next from c1
into @promotion_id, @EXPORT_DATE
while (@@fetch_status= 0)
begin
select @upcadded = count(*)
from T_LPM_support_upc_count (NOLOCK)
where promotion_id = @promotion_id
and processdate = @TODAY
and run_status = 2
and upc not in
(select upc from T_LPM_support_upc_count (NOLOCK)
where promotion_id = @promotion_id
and run_status = 1)
and processdate = @TODAY
select @upcdeleted = count(*)
from T_LPM_support_upc_count (NOLOCK)
where promotion_id = @promotion_id
and processdate = @TODAY
and run_status = 1
and upc not in
(select upc from T_LPM_support_upc_count (NOLOCK)
where promotion_id = @promotion_id
and run_status = 2)
and processdate = @TODAY
select @totalupcs = COUNT(UPC)
from T_LPM_support_upc_count (NOLOCK)
where promotion_id = @promotion_id
and processdate = @TODAY
and run_status = 2
select @STATUS = STATUS
from T_LPM_support_upc_count (NOLOCK)
where promotion_id = @promotion_id
and processdate = @TODAY
and run_status = 2
IF @UPCADDED > 0
SELECT @EXPORT_DATE = @TODAY
insert into T_LPM_SUPPORT_UPC_LIST
values (@promotion_id, @upcadded, @upcdeleted, @TODAY, @STATUS, @EXPORT_DATE, @totalupcs)
fetch next from c1
into @promotion_id, @EXPORT_DATE
end
close c1
deallocate c1
end
GO

You could turn the four queries inside the loop into four aggregate queries (adding the "cursor table" to each FROM clause, with a JOIN) storing the results of each into separate temp tables (SQL 7.0) or table variables (SQL 2000). This way you should have totals GROUPed BY promotion_id and run_status.
You should look at the logic from the F_PROMOTION_STATUS udf, and try to implement that logic in the main query statements, not by calling the udf.
Next, you can join those temp tables and insert the combined results into the final table, using the NOT IN subqueries to suppress the ones you don't want.
Finally (SQL 7.0) you should explicitly drop the temp tables.

Hi Adriaan,
Thanks for the quick reply. It is good option to do that way but will the table variables or the temp tables degrade the performance ...
I am using sql server 2000.

There's only one way to find out - create another sproc that uses the temp tables/table variables, and test them both.
If the number of iterations for the cursor is low, the solution with the cursor might be quicker than the set-based version. Difficult to say at what point the set-based version would be quicker.
So you need to test with large amounts of data - but realistic for what you expect to be processing in production.

Hi Adriaan ,
I will try to test the data with both the options . I have one more issue Do you have any idea regarding splitting the database files so that the application can use multithreadinig capability of the server . My sql procces now is taking around 8 hr over night batch and is a squential process.
The sql server is a 4 processor machine and it is utilising only one cpu.

Since your cursor-based proc is doing everything one step at a time, there is no point for SQL to use more than one cpu ...