• ColdPie (unregistered)

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

    @sequenceCode = @sequenceCode + 1

    Do I win?

  • Ytram (cs)

    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

  • Jake Heidt (unregistered) in reply to ColdPie

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

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

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

    daralick

  • Striker (unregistered)

    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 (cs) in reply to Jake Heidt

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

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

    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.

  • Anonymous (unregistered)

    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]'<span style="font-family: monospace;"><br> </span>  set @sequenceCode = right(convert(char(3), convert(int, @sequenceCode) + 101), 2)</> </>

  • travisowens (cs)

    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.

  • JRSTEELE (unregistered) in reply to daralick

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

  • retnuh (cs)

    declare @test char(2)

    set @test = '12'

    set @test = @test + 1

    select @test

  • res2 (cs) in reply to Jake Heidt

    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 (cs) in reply to travisowens
    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!
  • Anonymous (unregistered) in reply to res2
    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 (cs) in reply to Maurits
    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 (cs) in reply to daralick

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

    hmm, must remember review before posting...

  • Gene Wirchenko (cs)

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

    Sincerely,

    Gene Wirchenko

  • Paul Tomblin (unregistered) in reply to Maurits
    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?

  • Ross Presser (unregistered)

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

    elapsed time: 30 sec (including testing)

  • JRSTEELE (unregistered) in reply to JRSTEELE

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

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

    END

  • Wire (cs)

    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 (cs) in reply to Wire

    NT

  • Anonymous (unregistered) in reply to Wire
    Wire:

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


    Now test both with a value of 'XX'.

  • David (unregistered) in reply to Maurits

    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 (cs) in reply to David
    <span id="_ctl0_PostForm_Reply">On a side note, I cannot believe that people actually use the word 'leverage' in actual conversation. </span>


    You mean leverage the word 'leverage', right?
  • JRSTEELE (unregistered) in reply to Wire

    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 (cs) in reply to Wire

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

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

  • Volmarias (cs) in reply to travisowens
    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!

  • JRSTEELE (unregistered) in reply to 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.

  • DV (unregistered) in reply to David

    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 (cs) in reply to Volmarias

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

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

  • Mike R (cs)

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

    [H]

  • Wire (cs) in reply to JRSTEELE

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

  • Mike R (cs) in reply to Mike R
    Mike R:

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

    [H]

    Ahhcrap! someone did it already.

  • Hugo Kornelis (unregistered) in reply to JRSTEELE
    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 (cs) in reply to Wire

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

  • Brent Ashley (unregistered) in reply to Mike R

    this took me about 2 minutes and is quite similar:

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


  • joost (cs)
    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

  • David (unregistered) in reply to Ytram

    Ytram:
    <span id="_ctl0_PostForm_Reply">On a side note, I cannot believe that people actually use the word 'leverage' in actual conversation. </span>


    You mean leverage the word 'leverage', right?

    [+o(]....bastard...

     

    [:P]

  • endo (unregistered) in reply to joost
    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 (cs) in reply to Brent Ashley

    Slightly shorter, uses implicit casting...

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

  • Anonymous (unregistered) in reply to JRG
    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 (cs) in reply to Anonymous

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

  • JRG (cs) in reply to Anonymous

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

    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

  • David Ostroske (unregistered)

    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
  • Anonymous (unregistered) in reply to JRG
    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 (cs) in reply to JRG

    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 (cs) in reply to Bustaz Kool
    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.

Leave a comment on “Fix The WTF”

Log In or post as a guest

Replying to comment #:

« Return to Article