CREATE TABLE Cargo(
[CargoCode] [varchar](16) ,
)
Insert Into Cargo(CargoCode) Select 'aa20110827001'
Insert Into Cargo(CargoCode) Select 'aa20110827002'
Insert Into Cargo(CargoCode) Select 'aa20110827003'
Insert Into Cargo(CargoCode) Select 'aa20110827005'
Declare @CargoCode varchar(16)
Declare @SQL NVarchar(4000)
Set @SQL = 'Select @CargoCode = ''aa' + CONVERT(varchar(8), GETDATE(), 112)+ ''' + Right(''000'' + Ltrim(Rtrim(cast(Right(MAX(CargoCode), 3)+1 as varchar(3)))), 3)'
Set @SQL = @SQL + ' From Cargo'
Set @SQL = @SQL + ' where CargoCode Like ''aa' + CONVERT(varchar(8), GETDATE(), 112) + '%'''
Exec sp_executesql @SQL,N'@CargoCode Varchar(16) output',@CargoCode output
Select @CargoCode
Drop Table Cargo
本文出自“cnming”