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
