SQL Cursor Logic

Question

SQL Cursor Logic

Minnu

SSCertifiable

Points: 6451
More actions
May 25, 2015 at 6:17 am

#298603

Hi Team,
Using below table.
UserStrg_1Strg_2
--------------------------------
MiamiTestTest_1
MiamiTest_1Test_2
MiamiTest_2Test_3
MiamiTest_3Test_4
MiamiTest_4Test_5
to replace the strg_1 value with Strg_2 value using below cursor, but the cursor is iterating 5 times and the select query in below cursor is also returning 5 times, and the replace function is not working.
Declare @text1 varchar(max)
Declare @test2 varchar(max)
DECLARE Test_CUR CURSOR FOR SELECT col1, col2
FROM FNX_LNX WITH (NOLOCK)
WHERE
RTRIM(city)='Miami'
OPEN Test_CUR
FETCH NEXT FROM Test_CUR INTO @text1, @text2
WHILE @@FETCH_STATUS = 0
BEGIN
select id,name,
LTRIM(RTRIM(REPLACE(name,@text1,@text2))) New_name
from UAT
FETCH NEXT FROM Test_CUR INTO @text1, @text2
END
CLOSE Test_CUR
DEALLOCATE Test_CUR
Please help to to resolve the logic.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 1

Minnu (5/25/2015)
Hi Team,
Using below table.
UserStrg_1Strg_2
--------------------------------
MiamiTestTest_1
MiamiTest_1Test_2
MiamiTest_2Test_3
MiamiTest_3Test_4
MiamiTest_4Test_5
to replace the strg_1 value with Strg_2 value using below cursor, but the cursor is iterating 5 times and the select query in below cursor is also returning 5 times, and the replace function is not working.
Declare @text1 varchar(max)
Declare @test2 varchar(max)
DECLARE Test_CUR CURSOR FOR SELECT col1, col2
FROM FNX_LNX WITH (NOLOCK)
WHERE
RTRIM(city)='Miami'
OPEN Test_CUR
FETCH NEXT FROM Test_CUR INTO @text1, @text2
WHILE @@FETCH_STATUS = 0
BEGIN
select id,name,
LTRIM(RTRIM(REPLACE(name,@text1,@text2))) New_name
from UAT
FETCH NEXT FROM Test_CUR INTO @text1, @text2
END
CLOSE Test_CUR
DEALLOCATE Test_CUR
Please help to to resolve the logic.

First, you've been around long enough to know how to post data to get the quickest response. If not, please see the article at the first link in my signature lines below under "Helpful Links". The extra couple of minutes you spend on such a thing will make it much easier for someone to help you and you'll get tested code in return.

Second, I don't know why you would want to duplicate the data in one column in another column. Please explain why this is necessary because it's a waste of disk and memory space due to the duplication of data.

Third, you definitely do NOT need a cursor for this. The following code will do it all for you. Of course, I've not tested it because you've not provided the data in the readily consumable format that's mentioned in the article I've referred you to.

Forth, you don't need REPLACE for this. Not sure it would work to begin with but I can't tell a thing from your code example. I'm going only by your description that you want to replace Strg_1 (the whole string) Strg_2 (the whole string).

UPDATE dbo.FNX_LNX

SET Strg_1 = LTRIM(RTRIM(Strg_2))

WHERE User = 'Miami'

;

I also strongly recommend avoiding the use of WITH(NOLOCK). It can allow duplicate data to occur.

Since your description appears to be contrary to what it looks like you were doing in the code, please don't hesitate to come back with added clarification to the problem along with some readily consumable test data.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)