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)
as
begin
    declare @EVEID char(15)
    set @EVEID='E00001'
    if not exists(select * from Category1)
      set @EVEID='E00001'
    else
      begin
          select @EVEID=  max(right(EVENTID,5)) from Category1
          select @EVEID=
          case
              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)
          end
      end
    insert Category1 values(@EVEID,@EVEDESC)
end

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.