CURSOR strangeness

Question

CURSOR strangeness

abotha-1117340

SSC-Addicted

Points: 423
More actions
November 12, 2012 at 6:25 am

#284626

I have a cursor behaving strangely. Please consider the following SQL:
DECLARE @condition bit
SET @condition = 0
DECLARE X CURSOR FOR
SELECT a + CASE WHEN @condition = 1 THEN '&' + b ELSE '' END
FROM table
OPEN X
DECLARE @value varchar(100)
FETCH NEXT FROM X INTO @value
....
Note, from the above example, columns a and b in the table are also of type varchar.
The problem comes in when I run this, I get the following error at the FETCH NEXT line:
The conversion of the varchar value '1234' overflowed an int column.
However, when I change the SQL to this, it works without error:
IF @condition = 1
DECLARE X CURSOR FOR
SELECT a + '&' + b
FROM table
ELSE
DECLARE X CURSOR FOR
SELECT a
FROM table
Can anyone explain to me why this is happening?

Viewing 5 posts - 1 through 4 (of 4 total)

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

CASE can do odd things to datatype precedences, especially with things like zero-length strings (which can default to numeric datatypes, oddly enough, since '' = 0, per SQL Server).

Try using varchar() variables instead of '&' and ''. See if that fixes it.

- 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

abotha-1117340 SSC-Addicted Points: 423 More actions · Answer 2

Yes, this is odd behaviour indeed. I tried using varchar() instead of '&' and '', but that did not solve the problem. I even tried replacing '' with something that wasn't zero-length, like '*', and that also didn't work. So, I guess for now I'm going to have to go with the IF ELSE solution.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 3

abotha-1117340 (11/12/2012)
Yes, this is odd behaviour indeed. I tried using varchar() instead of '&' and '', but that did not solve the problem. I even tried replacing '' with something that wasn't zero-length, like '*', and that also didn't work. So, I guess for now I'm going to have to go with the IF ELSE solution.

Why not avoid the problem altogether? There doesn't seem to be the need for a cursor here.

--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)

abotha-1117340 SSC-Addicted Points: 423 More actions · Answer 4

I only simplified the SQL to bring the point across. The real SQL is actually much more complex and I do think a CURSOR is necessary as every entry in the table must be processed differently with each CURSOR FETCH.