存储过程获得最新订单号

create proc proc_No
@id varchar(14) output,
@date varchar(20),
@tname varchar(50),
@col varchar(50),
@type char(2)
as
declare @aa nvarchar(14)
declare @sql nvarchar(255)
    set @sql=('select @aa=max('+@col+') from '+@tname+' where SUBSTRING('+@col+',3,8)=convert(varchar(8),CONVERT(datetime,'''+@date+'''),112)')
    exec sp_executesql @sql,N'@aa varchar(14) output',@aa output
    set @id=@aa
    if(@id is null)
        set @id=@type+convert(varchar(8),CONVERT(datetime,@date),112)+'001'
    else
        set @id=@type+convert(varchar(14),convert(numeric(14),substring(@id,3,14))+1)


declare @a varchar(14)
exec proc_No @a output,'2011-02-02','coures','cid','DD'
print @a

 调用：

 /// <summary>
        /// 通用流水号
        /// </summary>
        /// <param name="procName">存储过程名</param>
        /// <param name="tabName">表名</param>
        /// <param name="ColName">列名</param>
        /// <param name="QName">前缀类型名</param>
        /// <param name="date">时间</param>
        /// <returns></returns>
        public static string SqlCommandNum(string procName, string tabName, string ColName, string QName, string date)
        {
            using (SqlConnection conn = new SqlConnection(strconn))
            {
                if (conn.State == ConnectionState.Closed)
                {
                    conn.Open();
                }
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                cmd.CommandText = procName;
                cmd.CommandType = CommandType.StoredProcedure;
                SqlParameter p1 = new SqlParameter("@id", SqlDbType.VarChar, 14);
                p1.Direction = ParameterDirection.Output;
                SqlParameter p2 = new SqlParameter("@type", QName);
                SqlParameter p5 = new SqlParameter("@date", date);
                SqlParameter p3 = new SqlParameter("@tname", tabName);
                SqlParameter p4 = new SqlParameter("@col", ColName);
                cmd.Parameters.Add(p1);
                cmd.Parameters.Add(p2);
                cmd.Parameters.Add(p3);
                cmd.Parameters.Add(p4);
                cmd.Parameters.Add(p5);
                cmd.ExecuteNonQuery();
                string a = p1.Value.ToString();
                return a;
            }
        }

 

转载于:https://www.cnblogs.com/wzq806341010/p/3178892.html