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]
« PrevPage 1 | Page 2 | Page 3Next »

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
42912 in reply to 42910
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
42915 in reply to 42912
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
42920 in reply to 42913
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
42922 in reply to 42912

Anonymous:
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.


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
42923 in reply to 42919
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
42924 in reply to 42922
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
42925 in reply to 42915
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
42926 in reply to 42916
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
42929 in reply to 42915
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
42932 in reply to 42920

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
42934 in reply to 42933
NT

Re: Fix The WTF

2005-09-06 14:48 • by Anonymous
42935 in reply to 42933
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
42936 in reply to 42915

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


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
42937 in reply to 42936
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
42938 in reply to 42933

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
42939 in reply to 42933
...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
42940 in reply to 42919
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
42941 in reply to 42938
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
42942 in reply to 42936

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
42943 in reply to 42940
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
42945 in reply to 42938
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
42946 in reply to 42944
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
42947 in reply to 42938
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
42948 in reply to 42945
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
42949 in reply to 42944
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
42952 in reply to 42937

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
42955 in reply to 42950
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
42956 in reply to 42949
Slightly shorter, uses implicit casting...

SELECT RIGHT('0'+CONVERT(VARCHAR,@sequenceCode+1),2)

Re: Fix The WTF

2005-09-06 15:26 • by Anonymous
42957 in reply to 42956
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
42959 in reply to 42957
Sorry, everyone's talking about T-SQL though...

Re: Fix The WTF

2005-09-06 15:31 • by JRG
42960 in reply to 42957

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


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 @sequenceCode
END

Re: Fix The WTF

2005-09-06 15:51 • by Anonymous
42965 in reply to 42960
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: 257
SQL 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
42966 in reply to 42956

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.

Re: Fix The WTF

2005-09-06 16:02 • by Bustaz Kool
42967 in reply to 42966
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.

« PrevPage 1 | Page 2 | Page 3Next »

Add Comment