The Daily WTF: Curious Perversions in Information Technology

2005-09-06 Reply Admin

I know absolutely nothing about T-SQL, but if I had to guess, would this work?

@sequenceCode = @sequenceCode + 1

Do I win?

Ytram · 2005-09-06 Reply Admin

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

2005-09-06 Reply Admin

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.

daralick · 2005-09-06 Reply Admin

set @sequenceCode = cast(@sequenceCode as int) + 1 if len(@sequenceCode) < 2 Set @sequenceCode = '0' + @sequenceCode

if len(@sequenceCode) > 2 Set @sequenceCode = '00'

daralick

2005-09-06 Reply Admin

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)

Maurits · 2005-09-06 Reply Admin

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

daralick · 2005-09-06 Reply Admin

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

daralick

Mung Kee · 2005-09-06 Reply Admin

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.

2005-09-06 Reply Admin

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

travisowens · 2005-09-06 Reply Admin

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.

2005-09-06 Reply Admin

SET @sequenceCode = LPAD( CONVERT( Char(2), (CAST(@sequenceCode as int) + 1) % 100), 2, '0')

retnuh · 2005-09-06 Reply Admin

declare @test char(2)

set @test = '12'

set @test = @test + 1

select @test

res2 · 2005-09-06 Reply Admin

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

kipthegreat · 2005-09-06 Reply Admin

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!

2005-09-06 Reply Admin

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)

Richard Nixon · 2005-09-06 Reply Admin

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!

daralick · 2005-09-06 Reply Admin

set @sequenceCode = right( ('0' + cast( (cast(@sequenceCode as int) + 1) as varchar(3))) , 2)

hmm, must remember review before posting...

Gene Wirchenko · 2005-09-06 Reply Admin

And when the sequence code is "00" (as after the "99" case is incremented)?

Sincerely,

Gene Wirchenko

2005-09-06 Reply Admin

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?

2005-09-06 Reply Admin

set @sequenceCode = right( cast ( 101 + cast( @sequenceCode as int) as char(3)) , 2)

elapsed time: 30 sec (including testing)

2005-09-06 Reply Admin

IF (CAST(@sequenceCode as int) > 0) BEGIN

SET @sequenceCode = LPAD( CONVERT( Char(2), (CAST(@sequenceCode as int) + 1) % 100), 2, '0')

END

Wire · 2005-09-06 Reply Admin

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

Wire · 2005-09-06 Reply Admin

NT

2005-09-06 Reply Admin

Wire:

The following replicates the '00' does not increment of the original:

Now test both with a value of 'XX'.

2005-09-06 Reply Admin

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

Ytram · 2005-09-06 Reply Admin

On a side note, I cannot believe that people actually use the word 'leverage' in actual conversation.

You mean leverage the word 'leverage', right?

2005-09-06 Reply Admin

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

Wire · 2005-09-06 Reply Admin

...and in checking for an lpad function I dropped the leading '0'.

SET @sequenceCode = right('0' + cast(@sequenceCode + 1 as varchar), 2)

Volmarias · 2005-09-06 Reply Admin

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!

2005-09-06 Reply Admin

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.

2005-09-06 Reply Admin

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.

Volmarias · 2005-09-06 Reply Admin

Pretend that the forums kept the formatting so that it looked presentable.

Also, pretend that the second println statement starts with \t :)

Mike R · 2005-09-06 Reply Admin

set @sequenceCode = right('0'+convert(varchar,(convert(int,@sequenceCode ) + 1)),2);

[H]

Wire · 2005-09-06 Reply Admin

This doesn't pad properly - the Char(2) includes a trailing space on single digit numbers, so '01' returns '2 '

Mike R · 2005-09-06 Reply Admin

Mike R:

set @sequenceCode = right('0'+convert(varchar,(convert(int,@sequenceCode ) + 1)),2);

[H]

Ahhcrap! someone did it already.

2005-09-06 Reply Admin

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

Wire · 2005-09-06 Reply Admin

I can't believe how much energy I've spent on this (not much, but still). Maybe I should get back to work.

2005-09-06 Reply Admin

this took me about 2 minutes and is quite similar:

set @sequenceCode = right(convert(char(3),convert(int,'1'+@sequenceCode)+1),2)

joost · 2005-09-06 Reply Admin

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

2005-09-06 Reply Admin

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]

2005-09-06 Reply Admin

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

JRG · 2005-09-06 Reply Admin

Slightly shorter, uses implicit casting...

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

2005-09-06 Reply Admin

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

JRG · 2005-09-06 Reply Admin

Sorry, everyone's talking about T-SQL though...

JRG · 2005-09-06 Reply Admin

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.

msumerano · 2005-09-06 Reply Admin

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

2005-09-06 Reply Admin

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

2005-09-06 Reply Admin

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.

Bustaz Kool · 2005-09-06 Reply Admin

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 · 2005-09-06 Reply Admin

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.

Fix The WTF

Leave a comment on “Fix The WTF”