CURSOR strangeness

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » CURSOR strangeness

CURSOR strangeness

Rate Topic

Display Mode

Topic Options

Author

Message

abotha-1117340

Posted Monday, November 12, 2012 6:25 AM

Grasshopper

Group: General Forum Members
Last Login: Thursday, February 21, 2013 5:57 AM
Points: 11, Visits: 64

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?

Post #1383660

GSquared

Posted Monday, November 12, 2012 6:47 AM

SSCoach

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 1:55 PM
Points: 15,442, Visits: 9,571

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

Post #1383671

abotha-1117340

Posted Monday, November 12, 2012 7:09 AM

Grasshopper

Group: General Forum Members
Last Login: Thursday, February 21, 2013 5:57 AM
Points: 11, Visits: 64

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.

Post #1383680

Jeff Moden

Posted Monday, November 12, 2012 11:07 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:52 PM
Points: 32,925, Visits: 26,813

abotha-1117340 (11/12/2012)

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."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #1383790

abotha-1117340

Posted Monday, November 12, 2012 11:34 PM

Grasshopper

Group: General Forum Members
Last Login: Thursday, February 21, 2013 5:57 AM
Points: 11, Visits: 64

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.

Post #1383949

« Prev Topic | Next Topic »

Permissions