I need to change the with out cursor in the code?
-
HI,
I need to change the procedure with out cursor in the code?
i want to use any other loop like while or if... i nstaed of cursor?
What modifications can i do?
COde is :
CREATE PROCEDURE [dbo].[SIRealEstateMortgage_Affected_Operational_To_Reporting]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE
@security_instrument_other_workflow_identifier int,
@security_instrument_real_estate_workflow_identifier int,
@workflow_identifier int,
@workflow_activity_identifier int,
@data_effective_start_date smalldatetime,
@data_effective_end_date smalldatetime,
@alternate_secondary_reference_type_identification varchar(50)
DECLARE @SIRealEstate_Affected_Op_To_Rep_Temp TABLE
(
security_instrument_other_workflow_identifier int,
security_instrument_real_estate_workflow_identifier int,
data_effective_start_date smalldatetime,
data_effective_end_date smalldatetime
)
/* create cursor to run through the records in reporting_migration_transaction*/
DECLARE SIRealEstate_Affected_migration_cursor_select CURSOR READ_ONLY FOR
SELECT primary_reference_type_identifier,secondary_reference_type_identifier,alternate_secondary_reference_type_identification
FROM reporting_migration_transaction
WHERE reference_type_code='WF'
AND dls_reporting_workflow_type_code='SIOWMAS'
/* use same date for all inserted records*/
select @data_effective_start_date = getdate();
/* Open the cursor*/
OPEN SIRealEstate_Affected_migration_cursor_select
FETCH SIRealEstate_Affected_migration_cursor_select INTO @workflow_identifier,@workflow_activity_identifier,@alternate_secondary_reference_type_identification
WHILE @@fetch_status = 0
BEGIN
/*
QUERY DATA HERE
*/
--Security Instrument Other workflow identifier
select @security_instrument_other_workflow_identifier=wfa.workflow_identifier
from workflow_activity wfa,security_instrument_association sia
where sia.workflow_activity_identifier = wfa.workflow_activity_identifier
and wfa.workflow_activity_identifier = @workflow_activity_identifier
--Security Instrument Real Estate workflow identifier
select @security_instrument_real_estate_workflow_identifier=wfa.workflow_identifier
from workflow_activity wfa,security_instrument_association sia
where sia.security_instrument_workflow_activity_identifier = wfa.workflow_activity_identifier
and wfa.workflow_activity_identifier = @alternate_secondary_reference_type_identification
INSERT INTO @SIRealEstate_Affected_Op_To_Rep_Temp VALUES
(
@security_instrument_other_workflow_identifier ,
@security_instrument_real_estate_workflow_identifier,
@data_effective_start_date,
@data_effective_end_date
)
FETCH SIRealEstate_Affected_migration_cursor_select INTO @workflow_identifier,@workflow_activity_identifier,@alternate_secondary_reference_type_identification
END /*while loop*/
CLOSE SIRealEstate_Affected_migration_cursor_select
DEALLOCATE SIRealEstate_Affected_migration_cursor_select
/* Now select all the data from temp table for return */
SELECT * FROM @SIRealEstate_Affected_Op_To_Rep_Temp
END
GO
Thanks
-
This is not the best place to ask this question. This is "SQL Server Integration Services" forum. Try in the TSQL forum.
---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply