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

 here i'm trying to insert auto generation of values which i gave at here but it shows following error message

"      Msg 170, Level 15, State 1, Line 14
      Line 14: Incorrect syntax near ')'.       "

here is my statements can someone help me

begin   
    declare @CatID char(15)
    set @CatID='C00001'
          select @CatID=max(right(CategoryID,5)) from Category 
        select @CatID = 
(CASE
          when (@CatID >= 0 and @CatID<9) then 'C0000'+convert(varchar,@CatID+1)
              when (@CatID>=9 and @CatID<99) then 'C000'+convert(varchar,@CatID+1)
              when @CatID>=99 and @CatID<999 then 'C00'+convert(varchar,@CatID+1)
              when @CatID>=999 and @CatID<9999 then 'C0'+convert(varchar,@CatID+1)
              when @CatID>=9999 and @CatID<99999 then 'C'+convert(varchar,@CatID+1)
          end)

hello

Just try this
begin   
    declare @CatID char(15)
    set @CatID='C00001'
          select @CatID=max(right(CategoryID,5)) from Category 
        select @CatID = 
CASE
          when @CatID >= 0 and @CatID<9) then 'C0000'+convert(varchar,@CatID+1
              when @CatID>=9 and @CatID<99) then 'C000'+convert(varchar,@CatID+1
              when @CatID>=99 and @CatID<999 then 'C00'+convert(varchar,@CatID+1
              when @CatID>=999 and @CatID<9999 then 'C0'+convert(varchar,@CatID+1
              when @CatID>=9999 and @CatID<99999 then 'C'+convert(varchar,@CatID+1
          end

hello
just ignore previous reply of mine and try this

hello

Just try this
begin   
    declare @CatID char(15)
    set @CatID='C00001'
          select @CatID=max(right(CategoryID,5)) from Category 
        select @CatID = 
CASE
          when @CatID >= 0 and @CatID<9 then 'C0000'+convert(varchar,@CatID+1)
              when @CatID>=9 and @CatID<99 then 'C000'+convert(varchar,@CatID+1)
              when @CatID>=99 and @CatID<999 then 'C00'+convert(varchar,@CatID+1)
              when @CatID>=999 and @CatID<9999 then 'C0'+convert(varchar,@CatID+1)
              when @CatID>=9999 and @CatID<99999 then 'C'+convert(varchar,@CatID+1)
          end

hi,

you have missed an end in your query . check the below code

begin   
    declare @CatID char(15)
    set @CatID='C00001'
          select @CatID=max(right(CategoryID,5)) from Category 
        select @CatID = 
(CASE
          when (@CatID >= 0 and @CatID<9) then 'C0000'+convert(varchar,@CatID+1)
              when (@CatID>=9 and @CatID<99) then 'C000'+convert(varchar,@CatID+1)
              when @CatID>=99 and @CatID<999 then 'C00'+convert(varchar,@CatID+1)
              when @CatID>=999 and @CatID<9999 then 'C0'+convert(varchar,@CatID+1)
              when @CatID>=9999 and @CatID<99999 then 'C'+convert(varchar,@CatID+1)
          end)
end

Thanks for your reply
it is working but i have done this for auto generation but now i its working and also i have created table but i don't know in the table it is not creating any auto generation numbers can you help me
actually i don't know what to do after these statements 

create table Category
(
    CategoryID char(15) primary key ,
    CategoryDesc varchar(100)  null
)


create proc prc_CatID
@CatDesc varchar(100)
as
begin
    declare @CatID char(15)
    set @CatID='C00001'
    if not exists(select * from Category)
      set @CatID='C00001'
    else
      begin
          select @CatID=max(right(CategoryID,5)) from Category
          select @CatID=
          case
              when @CatID>=0 and @CatID<9 then 'C0000'+convert(varchar,@CatID+1)
              when @CatID>=9 and @CatID<99 then 'C000'+convert(varchar,@CatID+1)
              when @CatID>=99 and @CatID<999 then 'C00'+convert(varchar,@CatID+1)
              when @CatID>=999 and @CatID<9999 then 'C0'+convert(varchar,@CatID+1)
              when @CatID>=9999 and @CatID<99999 then 'C'+convert(varchar,@CatID+1)
          end
      end
    insert Category values(@CatID,@CatDesc)
end

hi,

change your procedure as below and check the output of @CatID and @CatDesc


create table Category
(
    CategoryID char(15) primary key ,
    CategoryDesc varchar(100)  null
)


create proc prc_CatID
@CatDesc varchar(100)
as
begin
    declare @CatID char(15)
    set @CatID='C00001'
    if not exists(select * from Category)
      set @CatID='C00001'
    else
      begin
          select @CatID=max(right(CategoryID,5)) from Category
          select @CatID=
          case
              when @CatID>=0 and @CatID<9 then 'C0000'+convert(varchar,@CatID+1)
              when @CatID>=9 and @CatID<99 then 'C000'+convert(varchar,@CatID+1)
              when @CatID>=99 and @CatID<999 then 'C00'+convert(varchar,@CatID+1)
              when @CatID>=999 and @CatID<9999 then 'C0'+convert(varchar,@CatID+1)
              when @CatID>=9999 and @CatID<99999 then 'C'+convert(varchar,@CatID+1)
          end
      end

    select  @CatID, @CatDesc

    insert Category values(@CatID,@CatDesc)
end

Try this here you need to change the following things which are in bold. It definitely works.

Create proc prc_CatID --'Krishnan'
@CatDesc varchar(100)
as
begin
    declare @CatID varchar(10)
    set @CatID='C00001'
    if not exists(select DistId from NewTable)
    set @CatID='C00001'
    else
      select @CatID=max(cast(right(DistId,5) as numeric(10))) from NewTable
select @CatId
    begin
      select @CatId=
      case
        when len(@CatID)=1 then 'C0000'+convert(varchar,cast(@CatID as numeric(10))+1)
        when len(@CatID)=2 then 'C000'+convert(varchar,cast(@CatID as numeric(10))+1)
        when len(@CatID)=3 then 'C00'+convert(varchar,cast(@CatID as numeric(10))+1)
        when len(@CatID)=4 then 'C0'+convert(varchar,cast(@CatID as numeric(10))+1)
        when len(@CatID)=5 then 'C'+convert(varchar,cast(@CatID as numeric(10))+1)
            --else  @CatId
          end
insert into NewTable (DistId, District) values (@CatId,@CatDesc)
select * from NewTable
    end
End

use this condition @CatID>=0 and @CatID<9 instead of len(@CatId)=1 of my previous post. my previous post working. but when it comes C00009 then its length is increasing as C000010 instead of C00010. but its working fine when it comes to C00010. So use the same condition as you have in the case statement.

if you feel that is confusing, here is the proc for you. (I know you will definitely confused. Once you get confused, you will definitely get cleared.)

Create proc prc_CatID --'Krishnan'
@CatDesc varchar(100)
as
begin
    declare @CatID varchar(10)
    set @CatID='000000'
    if not exists(select DistId from NewTable)
    set @CatID='000000'
    else
      select @CatID=max(cast(right(DistId,5) as numeric(10))) from NewTable
select @CatId
    begin
      select @CatId=
      case
        when cast(@CatID as numeric(10))>=0 and cast(@CatID as numeric(10))<9 then 'C0000'+convert(varchar,cast(@CatID as numeric(10))+1)
        when cast(@CatID as numeric(10))>=9 and cast(@CatID as numeric(10))<99 then 'C000'+convert(varchar,cast(@CatID as numeric(10))+1)
        when cast(@CatID as numeric(10))>=99 and cast(@CatID as numeric(10))<999 then 'C00'+convert(varchar,cast(@CatID as numeric(10))+1)
        when cast(@CatID as numeric(10))>=999 and cast(@CatID as numeric(10))<9999 then 'C0'+convert(varchar,cast(@CatID as numeric(10))+1)
        when cast(@CatID as numeric(10))>=9999 and cast(@CatID as numeric(10))<99999 then 'C'+convert(varchar,cast(@CatID as numeric(10))+1)
            --else  @CatId
          end
insert into NewTable (DistId, District) values (@CatId,@CatDesc)
select * from NewTable
    end
End

Thanks. Don't forget to reply. I just want to know whether you can handle this or not.