March 5, 2009 at 8:44 am
In the below procedure I am trying to using nested cursors to compare list of old and new ids.
Am Idoing something wrong I dont see it comparing .
DROP PROCEDURE validate_OLD_NEW_stmt_ids
Go
CREATE PROCEDURE validate_OLD_NEW_stmt_ids
@flag bit = 0 OUTPUT,
@strDifference VARCHAR(300) = 'NULL' OUTPUT
AS
BEGIN ---3
DECLARE @var_message VARCHAR(1000)
DECLARE @var_row_old_ids VARCHAR(100)
DECLARE @var_row_new_ids VARCHAR(100)
DECLARE @var_srids NVARCHAR(1000)
Declare @var_sr_ids_new nvarchar (1000)
DECLARE @cur_table_old_ids CURSOR
DECLARE @cur_table_new_ids CURSOR
---------------------------------------vaiable declaration----------------------------
SET NOCOUNT ON
SELECT @flag as [@flag], @strDifference as [@strDifference]
--------------------------------------vaiable declaration----------------------------
---------------------------------------cursor declaration----------------------------
--curosr for new ids
--cursor for old ids
SET @cur_table_old_ids = CURSOR
FOR
SELECT sr_id,sr_status FROM products WHERE sr_id IN (31797,35388)
---------------------------------------cursor declaration----------------------------
OPEN @cur_table_old_ids --open cursor 1
PRINT '1'
FETCH NEXT FROM @cur_table_old_ids INTO @var_srids,@var_row_old_ids;
WHILE @@FETCH_STATUS = 0
BEGIN----1
PRINT @var_srids
---------------------------------------cursor declaration----------------------------
SET @cur_table_new_ids =CURSOR
FOR
SELECT sr_id,sr_status FROM products WHERE sr_id IN (50183,50248)
---------------------------------------cursor declaration----------------------------
OPEN @cur_table_new_ids --open cursor 2
PRINT '2'
FETCH NEXT FROM @cur_table_new_ids INTO @var_sr_ids_new,@var_row_new_ids
PRINT @var_sr_ids_new
WHILE @@FETCH_STATUS = 0
BEGIN--2
----------------------------------
IF (@var_row_old_ids = @var_row_new_ids )
BEGIN
SET @flag = CASE @flag WHEN 0 THEN 1 ELSE 0 END
SET @strDifference = 'sr_status match old_value : ' + @var_row_old_ids + ' and the new value: '+ @var_row_new_ids
SET @var_message = 'sr_status flag: ' + Convert (varchar(10),@flag)
END
ELSE
BEGIN
SET @flag = CASE @flag WHEN 1 THEN 0 ELSE 0 END
SET @strDifference = 'sr_status match old_value : ' + @var_row_old_ids + ' and the new value: '+ @var_row_new_ids
SET @var_message = 'sr_status dont match and the flag : ' + Convert (varchar(10),@flag)
END
--------------------------------------
PRINT '3'
PRINT @var_sr_ids_new
PRINT @flag
PRINT @strDifference
FETCH NEXT FROM @cur_table_new_ids INTO @var_sr_ids_new,@var_row_new_ids
PRINT '4'
END----2
CLOSE @cur_table_new_ids
DEALLOCATE @cur_table_new_ids
FETCH NEXT FROM @cur_table_old_ids INTO @var_srids,@var_row_old_ids
PRINT '5'
END----1
CLOSE @cur_table_old_ids
DEALLOCATE @cur_table_old_ids
END---3
March 5, 2009 at 9:21 am
I'm afraid I don't see the purpose of nesting these cursors. The only reason I know of to do that is to take a value from the outer cursor and use it as part of the Where clause of the inner cursor.
What is it that you are trying to get here? If it's just comparing some values in some tables, you can do that more easily with a join than with a pair of cursors.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 5, 2009 at 10:22 am
Hi Thanks for your reply.
Firstly I would like to make the scenario clear.
Every time we add a new module to the code we do testing. we take production ids and the new ids. give the same inputs from different tables and compare the outputs .
So Basically I have set of old ids and new ids from table A.
I need to compare them.
I have to repeat the process for few other tables.
I hope this bit clear.
March 6, 2009 at 7:41 am
If you have two tables that you want to compare, you can join them and have the computer compare them very, very fast. Much faster than a cursor.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 9, 2009 at 11:00 am
You're asking us to diagnose a problem without telling us what your error is or your expected results from the comparison. If you truly believe a table JOIN is not the way you need to go, please post information about both and we will do our best to assist you.
But G-Squared is correct. A JOIN is faster & simplier than multiple cursors unless you're trying to accomplish something beyond a simple check of numbers.
March 9, 2009 at 11:04 am
Thanks for your response.
Mine is a little different situation and I could resolve it with the sqlpackages.
Thanks,
March 9, 2009 at 11:08 am
siri007 (3/9/2009)
Thanks for your response.Mine is a little different situation and I could resolve it with the sqlpackages.
Thanks,
Would you care to elaborate on this? You did ask for help, it is only common curtesy to show how you finally resolved your problem.
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply