SQL Server - What is the mistake in here i tried many times but i can't find answer

Asked By Mano on 26-Jul-10 07:48 AM
hi i try to create auto generation i have got the same as like this statements in using another method that is working but i have tried the following one it is not working can someone tell me what is the problem in this coding

 here is my created table coding

create table Category1
    EVENTID char(16) primary key ,
    CategorDesc varchar(100) not null

here is my procedure statemnts

create proc prc_EVENID
@EVEDESC varchar(100)
    declare @EVEID char(15)
    set @EVEID='E00001'
    if not exists(select * from Category1)
      set @EVEID='E00001'
          select @EVEID=  max(right(EVENTID,5)) from Category1
          select @EVEID=
              when @EVEID>=0 and @EVEID<9 then 'E0000'+convert(varchar,@EVEID+1)
              when @EVEID>=9 and @EVEID<99 then 'E000'+convert(varchar,@EVEID+1)
              when @EVEID>=99 and @EVEID<999 then 'E00'+convert(varchar,@EVEID+1)
              when @EVEID>=999 and @EVEID<9999 then 'E0'+convert(varchar,@EVEID+1)
              when @EVEID>=9999 and @EVEID<99999 then 'E'+convert(varchar,@EVEID+1)
    insert Category1 values(@EVEID,@EVEDESC)

as far as i know all of the statements ok except under Begin "    select @EVEID=  max(right(EVENTID,5)) from Category1  "  can some one help me      
Peter Bromberg replied to Mano on 26-Jul-10 10:27 AM
Not sure about your logic here:

   select @EVEID=  max(right(EVENTID,5)) from Category1

How can you take the MAX of a character column that contains Alpha characters?

The first thing I noticed - Robbe Morris replied to Mano on 26-Jul-10 10:29 AM

was that EVEID is declared as a char(15) but you use it like an int or bigint in your case statement.

 The > < = operators work differently on string oriented datatypes than they do on numeric types.