converting cursor to select, different return

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 2005 Forums

Transact-SQL (2005)

converting cursor to select, different return

Author

Topic

chedderslam
Posting Yak Master

223 Posts

Posted - 2009-08-20 : 17:04:22

I am trying to change a cursor into a single insert select. The original cursor takes over 20 minutes and my new statement runs in under a minute. Unfortunately, I am getting 82343 rows from the original, and only 75653. It's a rather complex query for me, but I think I have every thing in the where clause and such so I don't know why I am missing records.

Thanks for any help.

A little long, but here are the two:

----------------------------------------------------------------------
Original:
----------------------------------------------------------------------
USE [sufi]

GO

/****** Object: StoredProcedure [dbo].[Policies_in_Force] Script Date: 08/20/2009 15:18:39 ******/
SET ansi_nulls ON

GO

SET quoted_identifier OFF

GO

/****** Object: Stored Procedure dbo.Policies_in_Force Script Date: 11/4/00 1:34:51 AM ******/
ALTER PROCEDURE [dbo].[Policies_in_force]
@start_month SMALLINT,
@start_year SMALLINT,
@end_month SMALLINT,
@end_year SMALLINT
AS

SET rowcount 0

DELETE FROM policies_in_force_report

SET rowcount 0

DECLARE @base VARCHAR(12),
@suffix SMALLINT,
@max_entered_date DATETIME,
@endorse_type VARCHAR(2),
@state VARCHAR(2),
@program CHAR(1),
@underwriting_approved DATETIME,
@start_date DATETIME,
@end_date DATETIME,
@in_force_date DATETIME

BEGIN TRY
SELECT @start_date = Convert(CHAR(2),@start_month) + '/1/' + Convert(CHAR(4),@start_year)

SELECT @end_date = Convert(CHAR(2),@end_month) + '/1/' + Convert(CHAR(4),@end_year)

WHILE @start_date <= @end_date
BEGIN
SELECT @in_force_date = Dateadd(dd,-1,Dateadd(mm,1,@start_date))

DECLARE policy_cursor CURSOR FOR
SELECT policy_base,
policy_suffix,
policy_state,
program
FROM policy (NOLOCK)
WHERE current_flag = 'Y'
AND expiration_date > @in_force_date
AND inception_date < @in_force_date

OPEN policy_cursor

FETCH NEXT FROM policy_cursor
INTO @base,
@suffix,
@state,
@program

WHILE (@@FETCH_STATUS = 0)
BEGIN
SELECT @underwriting_approved = underwriting_approved
FROM policy_information (nolock)
WHERE policy_base = @base
AND policy_suffix = @suffix

SELECT @max_entered_date = Max(entered_date)
FROM endorsement (nolock)
WHERE entered_date < @in_force_date
AND policy_base = @base
AND policy_suffix = @suffix

IF @underwriting_approved <= @in_force_date
BEGIN
IF NOT (@max_entered_date IS NULL)
BEGIN
SELECT @endorse_type = endorsement_type
FROM endorsement (nolock)
WHERE entered_date = @max_entered_date
AND policy_base = @base
AND policy_suffix = @suffix

IF @endorse_type <> 'CA'
BEGIN
INSERT INTO policies_in_force_report
(policy_base,
policy_suffix,
inforce_date,
policy_state,
program)
VALUES (@base,
@suffix,
@in_force_date,
@state,
@program)
END
END
ELSE
BEGIN
INSERT INTO policies_in_force_report
(policy_base,
policy_suffix,
inforce_date,
policy_state,
program)
VALUES (@base,
@suffix,
@in_force_date,
@state,
@program)
END
END

FETCH NEXT FROM policy_cursor
INTO @base,
@suffix,
@state,
@program
END

CLOSE policy_cursor

DEALLOCATE policy_cursor

SELECT @start_date = Dateadd(mm,1,@start_date)
END
END TRY

BEGIN CATCH
SELECT --returns the error number.
Error_number(),
--returns the complete text of the error message. The text includes the values supplied for any substitutable parameters such AS lengths, object names, or times.
Error_message(),
--returns the error severity.
Error_severity(),
--returns the error state number.
Error_state(),
--returns the line number inside the routine that caused the error.
Error_line(),
--returns the name of the stored procedure or trigger where the error occurred.
Error_procedure();
END CATCH

----------------------------------------------------------------------
New query:
----------------------------------------------------------------------
SET nocount ON

DECLARE @start_month SMALLINT,
@start_year SMALLINT,
@end_month SMALLINT,
@end_year SMALLINT,
@start_date DATETIME,
@end_date DATETIME,
@in_force_date DATETIME

SET @start_month = 1

SET @start_year = 2009

SET @end_month = 3

SET @end_year = 2009

SELECT @start_date = Convert(CHAR(2),@start_month) + '/1/' + Convert(CHAR(4),@start_year)

SELECT @end_date = Convert(CHAR(2),@end_month) + '/1/' + Convert(CHAR(4),@end_year)

DELETE FROM policies_in_force_report_dev

WHILE @start_date <= @end_date
BEGIN
SELECT @in_force_date = Dateadd(dd,-1,Dateadd(mm,1,@start_date))

INSERT policies_in_force_report_dev
(policy_base,
policy_suffix,
inforce_date,
policy_state,
program)
SELECT policy.policy_base,
policy.policy_suffix,
@in_force_date,
policy.policy_state,
policy.program
FROM policy (nolock)
INNER JOIN policy_information
ON policy.policy_base = policy_information.policy_base
AND policy.policy_suffix = policy_information.policy_suffix
INNER JOIN endorsement
ON policy.policy_base = endorsement.policy_base
AND policy.policy_suffix = endorsement.policy_suffix
WHERE policy.current_flag = 'Y'
AND policy.expiration_date > @in_force_date
AND policy.inception_date < @in_force_date
AND policy_information.underwriting_approved <= @in_force_date
GROUP BY policy.policy_base,
policy.policy_suffix,
policy.policy_state,
policy.program,
endorsement.entered_date,
endorsement_type
HAVING endorsement.entered_date = Max(endorsement.entered_date)
AND endorsement.entered_date < @in_force_date
AND ((Max(endorsement.entered_date) IS NOT NULL
AND endorsement.endorsement_type <> 'CA')
OR (Max(endorsement.entered_date) IS NULL))

SELECT @start_date = Dateadd(mm,1,@start_date)
END

SET nocount OFF

SELECT *
FROM policies_in_force_report_dev
ORDER BY policy_base,
policy_suffix
----------------------------------------------------------------------

chedderslam
Posting Yak Master

223 Posts

Posted - 2009-08-21 : 10:36:24

any help? I can't figure out why they are returning different sets...

IncisiveOne
Starting Member

36 Posts

Posted - 2009-08-22 : 18:36:35

Instead of assuming that 82343 rows is correct and 75653 is incorrect; since (technically) both figures are now in question, go to the data (use a smaller data sample for testing) and prove which figure is correct. The original might have been wrong all this time.

Just like you dropped the cursor on the inner loop, you can drop the cursor on the outer loop as well.

Cheers

Anything worth doing, is worth doing right.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources