Comment On Fix The WTF

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)

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.

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

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.

res2:

There is a second special case, if the code is 00 then it stays 00...

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

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

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

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

END

Wire:

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

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

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

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

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?!

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.

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.

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

[H]

Mike R:

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

[H]

Ahhcrap! someone did it already.

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

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

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]

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:

Slightly shorter, uses implicit casting...

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

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.

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

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

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.

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.