SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER  procedure pGetNewKey
  @cTableName varchar(20) = 'tOrderForm',
  @cKeyField varchar(15) = 'cCode',
  @iKeyLength integer = 12,
  @cResult varchar(15) output
as
  declare @cCode varchar(20),@cMax varchar(20),@cSerial varchar(4), @cSQL varchar(150), @cZero varchar(8)

  create table #a(cMaxCode varchar(10) null)

  if @iKeyLength>10
  begin
    set @cZero=substring('000000000000000',1,@iKeyLength-8)
    set @cSQL=
      'insert into #a select max(substring('+@cKeyField+',9,4)) cMaxCode from '+@cTableName+
      ' where substring('+@cKeyField+',1,8)=convert(char(8),getdate(),112)'
    Exec(@cSQL)

    select @cMax = cMaxCode from #a
   
    if @cMax is null
      set @cMax = '0'

    set @cSerial = convert(varchar(4),convert(int,@cMax)+1)

    set @cCode=convert(char(8),getdate(),112)+stuff(@cZero,@iKeyLength-8+1-len(@cSerial),len(@cSerial),@cSerial)
  end
  else
  begin
    set @cZero=substring('000000000000000',1,@iKeyLength)
    set @cSQL=
      'insert into #a select max('+@cKeyField+') cMaxCode from '+@cTableName

    Exec(@cSQL)

    select @cMax = cMaxCode from #a  

    if @cMax is null
      set @cMax = '0'

    set @cSerial = convert(varchar(4),convert(int,@cMax)+1)

    set @cCode=stuff(@cZero,@iKeyLength+1-len(@cSerial),len(@cSerial),@cSerial)
  end

  set @cResult = @cCode

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO