流水号生成存储过程

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- =============================================
-- Author:  <>
-- Create date: <2008-11-11>
-- Description: <流水号生成存储过程>
-- Description: <传入参数为要生成流水号的表的表名,输出参数为流水号>
-- =============================================
CREATE PROCEDURE [dbo].[GetSerialNumber]
@TableName varchar(50),     --传入要查询的表名
@Result varchar(50) OUTPUT  --返回的流水号
AS
BEGIN
----根据传入的表名,查询出需要的参数
 --declare @TableName varchar(50)
 --set @TableName = 'A010'
 declare @setId varchar(4)    --查询表名
 declare @keyType char(1)     --类型,默认1的才可以查询
 declare @keyTag varchar(10)  --查询前缀名称
 declare @keyYear int         --查询年长度
 declare @keyMonth int        --查询月长度
 declare @keyDay int          --查询日期长度
 declare @keyNumLen int       --查询后缀长度

 select @setId=setId,@keyTag=keyTag,@keyType=keyType,
 @keyYear=keyYear,@keyMonth=keyMonth,@keyDay=keyDay,@keyNumLen=keyNumLen
 from sr_setidkey
 where setid=@TableName and keyType = '1'

----流水号的前缀即@keyTag
----根据Sr_SetIdKey表中的值,返回日期的格式化形式 如20070509
 declare @DateString varchar(8)    ----当前日期
 declare @year varchar(4)
 declare @month varchar(2)
 declare @day varchar(2)

    set @year=convert(varchar(4),year(getdate()))
 set @month=convert(varchar(4),month(getdate()))
 set @day=convert(varchar(4),day(getdate()))
   
 if(@keyYear = 0)
  set @year= '';
 if(@keyYear = 2)
  set @year= subString(@year,3,4)
 if(@keyMonth = 0)
  set @month= '';
 if(@keyMonth = 2 and len(@month)=1)
  set @month=N'0'+@month
 if(@keyDay = 0)
  set @day= '';
 if(@keyDay = 2 and len(@day)=1)
  set @day=N'0'+@day
    SET @DateString = @year+@month+@day

--返回后缀的递增号,按查询出的长度生成,不足补0
 --流水号生成,返回当日最大流水号,否则,一律从001号开始生成
    --存在一个当日同前缀的流水号的条件:
    --1.相同的流水号前缀,即status1前缀名称必须与Sr_SetIdKey表中对应的keyTag字段内容相同,不区分大小写
    --2.相同的当日的中间日期

    declare @LastSheetNo varchar(50) --记录中最后一条流水号
 declare @sql nvarchar(4000)
 set @sql = 'SELECT TOP 1 @a = status1 FROM '+@TableName
    +' WHERE LEFT(status1,len('''+@keyTag+''')) = '''+@keyTag
    +''' AND Right(LEFT(status1,len('''+@keyTag+@DateString+''')),len('''+@DateString+''')) = '''+@DateString
    +''' ORDER BY status1 DESC'

 exec sp_executesql @sql,N'@a varchar(50) output',@LastSheetNo output
 --查询出后缀的字符串,转换成INT格式方便递增
    --定义好相关参数，比较是否有相同的流水号前缀
    --存在，获取最后一条流水+1
 declare @suffixStr int
 declare @SuffixResult varchar(50)

 if (@LastSheetNo is null or @LastSheetNo = '')
  set @suffixStr = 1
 else
  set @suffixStr = convert(int,substring(@LastSheetNo,len(@keyTag) + @keyYear + @keyMonth + @keyDay+1,@keyNumLen)) + 1
 --补全0
 declare @i int
 set @i=0
 declare @temp varchar(50)
 set @temp = ''
 while @i<(@keyNumLen-1)
  BEGIN
   SET @temp=@temp+'0'
   SET @i=@i+1
  END
 set @SuffixResult = @temp +  convert(varchar(50),@suffixStr)
 --declare @Result varchar(50)
 set @Result = @keyTag + @DateString + @SuffixResult
 --select @Result
END

 

 

        public static string ConnectionString = AnyConfig.DataAccess_MainDataString;
        public static SqlConnection conn;

 

        #region 连接字符串自定义属性
        /// <summary>
        /// 连接字符串自定义属性
        /// </summary>
        public string ConnStr
        {
            get
            {
                return ConnectionString;
            }
            set
            {
                ConnectionString = value;
            }
        }
        #endregion 测试链接

        #region 测试链接
        /// <summary>
        /// 测试链接
        /// </summary>
        /// <returns></returns>
        public bool testConn()
        {
            if (open())
            {
                this.closeConn();
                return true;
            }
            else
            {
                this.closeConn();
                return false;
            }
        }
        #endregion

 

 

        #region getSerialNumber执行带一个输出参数的存储过程
        /// <summary>
        /// 执行带一个输出参数的存储过程
        /// </summary>
        /// <param name="sql">存储过程的名称</param>
        /// <param name="paramenters">参数,存储过程中的参数
        /// SqlParameter[] Parament=new SqlParameter[2];
        ///Parament[0]=new SqlParameter("@sqlstr",SqlDbType.NVarChar,4000);
        ///Parament[0].Value=sql;
        ///Parament[1]=new SqlParameter("@currentpage",SqlDbType.Int,4);
        ///Parament[1].Value=curpage;</param>
        /// <returns>返回存储过程的输出参数</returns>
        public string getSerialNumber(string sql, params SqlParameter[] paramenters)
        {
            string result = "";
            if (open())
            {
                try
                {
                    SqlCommand cmd = new SqlCommand();
                    cmd.Connection = conn;
                    cmd.CommandText = sql;
                    if (paramenters != null)
                    {
                        foreach (SqlParameter parm in paramenters)
                            cmd.Parameters.Add(parm);
                    }
                    cmd.Parameters.Add("@Result", SqlDbType.VarChar, 50);
                    cmd.Parameters["@Result"].Direction = ParameterDirection.Output;
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.ExecuteNonQuery();

                    result = cmd.Parameters["@Result"].Value.ToString();
                }
                catch (Exception ex)
                {
                    SysDataLog.log(ex);
                    string str = ex.ToString();
                }
                finally
                {
                    closeConn();
                }
            }
            return result;
        }
        #endregion
    }

 

        #region 打开数据库连接
        /*
         *打开数据库连接
         */
        private bool open()
        {
            bool bl = true;
            try
            {
                conn = new SqlConnection(ConnectionString);
                conn.Open();
            }
            catch (Exception ex)
            {
                SysDataLog.log(ex);
                bl = false;
            }
            return bl;

        }
        #endregion

        #region 关闭数据库连接
        /// <summary>
        /// 关闭数据库连接
        /// </summary>
        /// <returns></returns>
        private bool closeConn()
        {
            try
            {
                if (conn.State.ToString() == "Open")
                    conn.Close();
                conn.Dispose();
                return true;//关闭成功，返回true
            }
            catch (Exception ex)
            {
                SysDataLog.log(ex);
                return false;//关闭失败，返回false   
            }
        }
        #endregion

 

        DataAccessBase dab = new DataAccessBase();

        string strTable = "a010";
        SqlParameter[] Parament = new SqlParameter[1];
        Parament[0] = new SqlParameter("@tableName", SqlDbType.VarChar, 50);
        Parament[0].Value = strTable;

        lblNo.Text = dab.getSerialNumber("GetSerialNumber", Parament);