# Comment On Fix The WTF

Tracy McKibben was trudging through code on the hunt for a bug and came across today's example. I was staring at it for a little bit and it got me thinking ... it's time for a new challenge! Think back to the Spot The WTF that I post every once in a while. [expand full text]
 « Prev Page 1 | Page 2 | Page 3 Next »

### Re: Fix The WTF

2005-09-06 14:04 • by ColdPie
 I know absolutely nothing about T-SQL, but if I had to guess, would this work? @sequenceCode = @sequenceCode + 1 Do I win?

### Re: Fix The WTF

2005-09-06 14:09 • by Ytram
 All right, I've done a little bit of T-SQL, but just enough to do fairly standard CRUD stored procedures, but here goes: DECLARE @Result int SET @Result = CAST(@sequenceCode as int) + 1 RETURN @Result

### Re: Fix The WTF

2005-09-06 14:11 • by Jake Heidt
 You missed the fringe case of 99 = 00 :P I think it would go along the lines of. Declare @newval int Declare @parsedVal int Declare @out varchar Set @parsedVal = Cast(@sequenceCode as int) Set @newval = @parsedVal + 1 Set @out = Cast(@parsedVal as varchar) where @parsedVal >= 10 Set @out = '0' + Cast(@parsedVal as varchar) where @parsedVal < 10 Set @out = '00' where @parsedVal = 100 TSQL is so ugly. Obviously theres just a plain better way to do this.

### Re: Fix The WTF

2005-09-06 14:12 • by daralick
 set @sequenceCode = cast(@sequenceCode as int) + 1 if len(@sequenceCode) < 2 Set @sequenceCode = '0' + @sequenceCode if len(@sequenceCode) > 2 Set @sequenceCode = '00' daralick

### Re: Fix The WTF

2005-09-06 14:14 • by Striker
 In order to prove that I do too much in T-SQL, how about this?: set @sequenceCode = right( '0' + cast( cast( @sequenceCode as int) + 1 as varchar(3) ), 2)

### Re: Fix The WTF

2005-09-06 14:14 • by Maurits
 Prepwork.. CREATE TABLE sequencecodemap (     this char(2),     next char(2) ) INSERT INTO sequencecodemap (this, next) VALUES ('00', '01') INSERT INTO sequencecodemap (this, next) VALUES ('01', '02') ... INSERT INTO sequencecodemap (this, next) VALUES ('99', '00') Finally... SELECT     @SequenceCode = Next FROM     SequenceCodeMap WHERE     @SequenceCode = This

### Re: Fix The WTF

2005-09-06 14:14 • by daralick
 set @sequenceCode = cast(@sequenceCode as int) + 1 if len(@sequenceCode) < 2 Set @sequenceCode = '0' + @sequenceCode if len(@sequenceCode) > 2 Set @sequenceCode = '00' correction.. html hmm must need \ daralick

### Re: Fix The WTF

2005-09-06 14:18 • by Mung Kee
 I cannot overstate how truly amazed I am at the number of big stupid switch statements or if...else statements we see here.  Once the number of comparisons exceeds a handful, it may be time to start thinking about a more pragmatic solution.  I think a lot of developers lack this process of graduated thought.

### Re: Fix The WTF

2005-09-06 14:23 • by Anonymous
 I hate to admit it, but it took at least two minutes, with testing, for me to get it right:   <><>IF @sequenceCode like '[0-9][0-9]'   set @sequenceCode = right(convert(char(3), convert(int, @sequenceCode) + 101), 2)

### Re: Fix The WTF

2005-09-06 14:23 • by travisowens
 Ok let's dive into the original coder's mind and assume that there's no way to casting or type conversion in T-SQL. This code is the worst of all the repititve code I've seen on here in a long time, this guy really hand typed 100 conversions?! Would it have been better to reverse the string and add 1 to the first digit by doing his conversion, but only typed 0-9, and if we're at 9 and adding one, add one to the next digit, and so forth.  While it would have required *gasp* a loop, at least he could have done his conversion in 10 checks instead of 100. So what happens when his database needs to support more than 0-100, what about 100,000?  That's a whole month of copying and pasting.

### Re: Fix The WTF

2005-09-06 14:23 • by JRSTEELE
 SET @sequenceCode = LPAD( CONVERT( Char(2), (CAST(@sequenceCode as int) + 1) % 100), 2, '0')

### Re: Fix The WTF

2005-09-06 14:24 • by retnuh
 declare @test char(2) set @test = '12' set @test = @test + 1 select @test

### Re: Fix The WTF

2005-09-06 14:26 • by res2
 Anonymous:You missed the fringe case of 99 = 00 :PI think it would go along the lines of.Declare @newval intDeclare @parsedVal intDeclare @out varcharSet @parsedVal = Cast(@sequenceCode as int)Set @newval = @parsedVal + 1Set @out = Cast(@parsedVal as varchar) where @parsedVal >= 10Set @out = '0' + Cast(@parsedVal as varchar) where @parsedVal < 10Set @out = '00' where @parsedVal = 100TSQL is so ugly.Obviously theres just a plain better way to do this. There is a second special case, if the code is 00 then it stays 00...

### Re: Fix The WTF

2005-09-06 14:29 • by kipthegreat
 travisowens:So what happens when his database needs to support more than 0-100, what about 100,000?  That's a whole month of copying and pasting. That's what interns are for!

### Re: Fix The WTF

2005-09-06 14:30 • by Anonymous
 res2:There is a second special case, if the code is 00 then it stays 00... Dang!  Missed that.  I did catch that it remains unchanged if it isn't two numeric digits. Heres the modified code:   IF @sequenceCode like '[0-9][0-9]' and @sequenceCode <> '00'   set @sequenceCode = right(convert(char(3), convert(int, @sequenceCode) + 101), 2)

### Re: Fix The WTF

2005-09-06 14:35 • by Richard Nixon
 Maurits:Prepwork.. CREATE TABLE sequencecodemap (     this char(2),     next char(2) ) INSERT INTO sequencecodemap (this, next) VALUES ('00', '01') INSERT INTO sequencecodemap (this, next) VALUES ('01', '02') ... INSERT INTO sequencecodemap (this, next) VALUES ('99', '00') Finally... SELECT     @SequenceCode = Next FROM     SequenceCodeMap WHERE     @SequenceCode = This Good show!

### Re: Fix The WTF

2005-09-06 14:35 • by daralick
 set @sequenceCode = right( ('0' + cast( (cast(@sequenceCode as int) + 1) as varchar(3))) , 2) hmm, must remember review before posting...

### Re: Fix The WTF

2005-09-06 14:39 • by Gene Wirchenko
 And when the sequence code is "00" (as after the "99" case is incremented)? Sincerely, Gene Wirchenko

### Re: Fix The WTF

2005-09-06 14:43 • by Paul Tomblin
 Maurits:Prepwork.. CREATE TABLE sequencecodemap (     this char(2),     next char(2) ) INSERT INTO sequencecodemap (this, next) VALUES ('00', '01') INSERT INTO sequencecodemap (this, next) VALUES ('01', '02') ... snip You're just trying to get into next week's WTF, right?

### Re: Fix The WTF

2005-09-06 14:44 • by Ross Presser
 set @sequenceCode = right( cast ( 101 + cast( @sequenceCode as int) as char(3)) , 2) elapsed time: 30 sec (including testing)

### Re: Fix The WTF

2005-09-06 14:45 • by JRSTEELE
 IF (CAST(@sequenceCode as int) > 0) BEGIN    SET @sequenceCode = LPAD( CONVERT( Char(2), (CAST(@sequenceCode as int) + 1) % 100), 2, '0') END

### Re: Fix The WTF

2005-09-06 14:45 • by Wire
 JRSTEELE - T-SQL does not support lpad The following replicates the '00' does not increment of the original:      IF (@sequenceCode BETWEEN '01' AND '99') BEGIN           SET @sequenceCode = right(cast(@sequenceCode + 1 as varchar), 2)      END ELSE BEGIN           SET @sequenceCode = '00'      END

### I hate this fucking forum software.

2005-09-06 14:45 • by Wire
 NT

### Re: Fix The WTF

2005-09-06 14:48 • by Anonymous
 Wire: The following replicates the '00' does not increment of the original: Now test both with a value of 'XX'.

### Re: Fix The WTF

2005-09-06 14:49 • by David
 Maurits:Prepwork..CREATE TABLE sequencecodemap(    this char(2),    next char(2))INSERT INTO sequencecodemap (this, next)VALUES ('00', '01')INSERT INTO sequencecodemap (this, next)VALUES ('01', '02')...INSERT INTO sequencecodemap (this, next)VALUES ('99', '00')Finally...SELECT    @SequenceCode = NextFROM    SequenceCodeMapWHERE    @SequenceCode = This Best.  Solution.  Evar.  Truly leveraging the full dynamic range of your core competencies as well as the available technology.   On a side note, I cannot believe that people actually use the word 'leverage' in actual conversation.  Not even business speak, but real actual person to person colloquial conversation.  Apparently they're too good for the word 'use.'

### Re: Fix The WTF

2005-09-06 14:53 • by Ytram
 On a side note, I cannot believe that people actually use the word 'leverage' in actual conversation. You mean leverage the word 'leverage', right?

### Re: Fix The WTF

2005-09-06 14:54 • by JRSTEELE
 No LPAD??? OK... IF (CAST(@sequenceCode as int) > 0) BEGIN    SET @sequenceCode = RIGHT( '0' + CONVERT( Char(2), (CAST(@sequenceCode as int) + 1) % 100), 2) END   This code a) does not increment the value if @sequenceCode = '00' or is equla to anyother nonnumeric value (like the original). b) pads the left with a 0 is the number is less than 10 (like the original) c) returns the value in the original variable @sequenceCode (like the original).

### Re: Fix The WTF

2005-09-06 14:55 • by Wire
 ...and in checking for an lpad function I dropped the leading '0'. SET @sequenceCode = right('0' + cast(@sequenceCode + 1 as varchar), 2)

### Re: Fix The WTF

2005-09-06 15:02 • by Volmarias
 travisowens:This code is the worst of all the repititve code I've seen on here in a long time, this guy really hand typed 100 conversions?! No, of course he didn't! He wrote a program to give him those cases! For your viewing pleasure, I give you: WTFGenerator, in Java! public class WTFGenerator { public static void main(String[] args) { for(int i = 1; i!=100; ;) { System.out.println("IF @sequenceCode = '" + prettyMethod(x++) + "'"); System.out.println("SET @sequenceCode = '" + prettyMethod(x) + "'"); } } public String prettyMethod (int x) { if(x < 10) return "0" + x; return new String(x); } } Copy + Paste and there you go! Now, I haven't actually tried to compile this, so it probably doesn't work, but I think that I've solved today's WTF!

### Re: Fix The WTF

2005-09-06 15:03 • by JRSTEELE
 Anonymous: No LPAD??? OK... IF (CAST(@sequenceCode as int) > 0) BEGIN    SET @sequenceCode = RIGHT( '0' + CONVERT( Char(2), (CAST(@sequenceCode as int) + 1) % 100), 2) END   This code a) does not increment the value if @sequenceCode = '00' or is equla to anyother nonnumeric value (like the original). b) pads the left with a 0 is the number is less than 10 (like the original) c) returns the value in the original variable @sequenceCode (like the original).   I give up... Wire wins!  I've been told my condition will die, and then I noticed the Modulo 100 isn't neccesary.

### Re: Fix The WTF

2005-09-06 15:03 • by DV
 Hmm. Why not make it a local temporary table inside of a stored procedure so that it gets created on every call.  Don't want to mess up a pristine (I'm assuming) data model with a look up table.

### Re: Fix The WTF

2005-09-06 15:03 • by Volmarias
 Pretend that the forums kept the formatting so that it looked presentable. Also, pretend that the second println statement starts with \t :)

### Re: Fix The WTF

2005-09-06 15:04 • by Mike R
 set @sequenceCode =  right('0'+convert(varchar,(convert(int,@sequenceCode ) + 1)),2); [H]

### Re: Fix The WTF

2005-09-06 15:06 • by Wire
 This doesn't pad properly - the Char(2) includes a trailing space on single digit numbers, so '01' returns '2 '

### Re: Fix The WTF

2005-09-06 15:06 • by Mike R
 Mike R: set @sequenceCode =  right('0'+convert(varchar,(convert(int,@sequenceCode ) + 1)),2); [H] Ahhcrap! someone did it already.

### Re: Fix The WTF

2005-09-06 15:07 • by Hugo Kornelis
 Anonymous: This code d) Results in an error: "Incorrect syntax near ')'", because there is no END to match the BEGIN e) (After removing unneeded BEGIN) Yields run-time error if @sequenceCode is not numeric: "Syntax error converting the varchar value 'XX' to a column of data type int." Best, Hugo

### Re: Fix The WTF

2005-09-06 15:07 • by Wire
 I can't believe how much energy I've spent on this (not much, but still). Maybe I should get back to work.

### Re: Fix The WTF

2005-09-06 15:09 • by Brent Ashley
 this took me about 2 minutes and is quite similar: set @sequenceCode = right(convert(char(3),convert(int,'1'+@sequenceCode)+1),2)

### Re: Fix The WTF

2005-09-06 15:10 • by joost
 Alex Papadimoulis:[...] and is best enjoyed if you've never programmed anything in T-SQL in your life before. [...] `SET @number = INT(@sequenceCode)IF @number > 0 SET @number = @number + 1 IF @number = 100 SET @number = 0 IF @number < 10  SET @sequenceCode = '0' + STR(@number) ELSE SET @sequenceCode = STR(@number)DROP DATABASE`

### Re: Fix The WTF

2005-09-06 15:14 • by David
 Ytram:On a side note, I cannot believe that people actually use the word 'leverage' in actual conversation. You mean leverage the word 'leverage', right? [+o(]....bastard...   [:P]

### Re: Fix The WTF

2005-09-06 15:22 • by endo
 joost: Alex Papadimoulis:[...] and is best enjoyed if you've never programmed anything in T-SQL in your life before. [...] `SET @number = INT(@sequenceCode)IF @number > 0 SET @number = @number + 1 IF @number = 100 SET @number = 0 IF @number < 10  SET @sequenceCode = '0' + STR(@number) ELSE SET @sequenceCode = STR(@number)DROP DATABASE` Nice, no one will miss that database anyway :)

### Re: Fix The WTF

2005-09-06 15:23 • by JRG
 Slightly shorter, uses implicit casting...`SELECT RIGHT('0'+CONVERT(VARCHAR,@sequenceCode+1),2) `

### Re: Fix The WTF

2005-09-06 15:26 • by Anonymous
 JRG:Slightly shorter, uses implicit casting...`SELECT RIGHT('0'+CONVERT(VARCHAR,@sequenceCode+1),2) ` Were we told what the database product was? com.sybase.jdbc2.jdbc.SybSQLException: Implicit conversion from datatype 'VARCHAR' to 'INT' is not allowed.  Use the CONVERT function to run this query. Error code: 257 SQL state: 42000

### Re: Fix The WTF

2005-09-06 15:30 • by JRG
 Sorry, everyone's talking about T-SQL though...

### Re: Fix The WTF

2005-09-06 15:31 • by JRG
 Anonymous: JRG:Slightly shorter, uses implicit casting...`SELECT RIGHT('0'+CONVERT(VARCHAR,@sequenceCode+1),2) `Were we told what the database product was?com.sybase.jdbc2.jdbc.SybSQLException: Implicit conversion from datatype 'VARCHAR' to 'INT' is not allowed.  Use the CONVERT function to run this query.Error code: 257SQL state: 42000 Actually, it's there in the OPs message... T-SQL.

### Re: Fix The WTF

2005-09-06 15:31 • by msumerano
 Handles anything in the SequenceNumber and properly "wraps" around when the passed in sequence number is 99.```declare @SequenceNumber char(2) select @SequenceNumber = '95' declare @sn int if (isnumeric(@SequenceNumber) = 1) select @sn = convert(int, @SequenceNumber) else select @sn = 0 if (@sn < 0 or @sn >= 99) select @SequenceNumber = '00' else select @SequenceNumber = right('0' + convert(varchar, (@sn + 1)), 2) select @SequenceNumber ```

### Re: Fix The WTF

2005-09-06 15:42 • by David Ostroske
 Not the most elegant solution. But it works. And it won't barf when not-numeric characters are passed in.`SET @sequenceCode = CASE  WHEN @sequenceCode = '00'    THEN '00'  WHEN @sequenceCode LIKE '[0-9][0-9]'    THEN RIGHT('00' + CAST(CAST(@sequenceCode AS INTEGER)+1 AS VARCHAR(3)), 2)    ELSE @sequenceCodeEND`

### Re: Fix The WTF

2005-09-06 15:51 • by Anonymous
 JRG: Anonymous: JRG:Slightly shorter, uses implicit casting...`SELECT RIGHT('0'+CONVERT(VARCHAR,@sequenceCode+1),2) `Were we told what the database product was?com.sybase.jdbc2.jdbc.SybSQLException: Implicit conversion from datatype 'VARCHAR' to 'INT' is not allowed.  Use the CONVERT function to run this query.Error code: 257SQL state: 42000 Actually, it's there in the OPs message... T-SQL. Sybase invented T-SQL.  Microsoft, having acquired rights to use it, has modified it in their product.  "T-SQL" by itself doesn't tell you if the implicit cast will work.

### Re: Fix The WTF

2005-09-06 16:01 • by Bustaz Kool
 JRG:Slightly shorter, uses implicit casting...`SELECT RIGHT('0'+CONVERT(VARCHAR,@sequenceCode+1),2) ` As far as I can discern, all of the one liners throw an error on invalid input.
 Bustaz Kool: JRG:Slightly shorter, uses implicit casting...`SELECT RIGHT('0'+CONVERT(VARCHAR,@sequenceCode+1),2) ` As far as I can discern, all of the one liners throw an error on invalid input. And most of the multiple liners, too.