dongdong

例如：'CKDH20051101001','HTBH20051101008',存储过程如下
CREATE PROCEDURE PRXTGL_GetDJHM @TableName nvarchar(100),@ColumnName nvarchar(100),@Prefix nvarchar(10),@outDJHM nvarchar(20) OUTPUT
AS
BEGIN
    Declare @sqlstring nvarchar(2000)
    Set @sqlstring='Select @DJHM='''+@Prefix+''''-----------------前缀
                   +'+Str(Year(Getdate()),4,0)'--------------------年份
                   +'+Right('+'''00'''+'+Ltrim(Rtrim(Str(Month(GetDate()),2,0))),2)'-------------月份
                   +'+Right('+'''00'''+'+Ltrim(Rtrim(Str(Day(GetDate()),2,0))),2)'--------------------日期
                   +'+Right('+'''000'''+'+Ltrim(Rtrim(Str(Cast(IsNull(Max(Right('+@ColumnName+',3)),'+'''000'''+') As Int)+1,3,0))),3)'-----单号
                   +' From '+@TableName

                   +' Where Ltrim(Rtrim('+ @ColumnName+')) Like '
                   +'+'''+@Prefix+''''  ----------------前缀
                   +'+Str(Year(Getdate()),4,0)'-----------------------年份
                   +'+Right('''+'00'+'''+Ltrim(Rtrim(Str(Month(GetDate()),2,0))),2)'-----------月份
                   +'+Right('''+'00'+'''+Ltrim(Rtrim(Str(Day(GetDate()),2,0))),2)'-------------日期
                   +'+''_____'''

    Exec sp_executesql @sqlstring,N'@DJHM nvarchar(20) output',@DJHM=@outDJHM output
END