一个自动产生编号的存储过程，数据库sql2000或2005

    //示列.net调用

 

         /// <summary>
        /// 通过存储过程得到ID号
        /// </summary>
        /// <param name="IDName">编号名称</param>
        /// <param name="IDType">编号类型 1--关联日期如:0706040001 0--自然增长如:0003</param>
        /// <param name="IDLength">编号长度</param>
        public static string getID(string IDName, string IDType, int IDLength)
        {
            SqlConnection conn = new SqlConnection(SqlHelper.ConnStrng);
            string sRef = null;
            try
            {
                SqlCommand cmd = new SqlCommand("p_id", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                SqlParameter parameter = cmd.Parameters.Add("@pName", SqlDbType.VarChar, 20);
                parameter.Value = IDName;

                parameter = cmd.Parameters.Add("@pType", SqlDbType.VarChar, 20);
                parameter.Value = IDType;

                parameter = cmd.Parameters.Add("@pLeng", SqlDbType.Int);
                parameter.Value = IDLength;

                SqlParameter myPara = new SqlParameter("@pValue", SqlDbType.VarChar, 20);
                cmd.Parameters.Add(myPara);
                myPara.Direction = ParameterDirection.Output;
                conn.Open();
                cmd.ExecuteNonQuery();
                sRef = myPara.Value.ToString();
                cmd.Parameters.Clear();
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }
            return sRef;
        }

//proc code

CREATE proc p_id @pName varchar(20), @pType varchar(20),@pLeng int=10, @pValue varchar(20) out
as
declare @toDate char(6)
declare @tempDate char(6)
declare @numOrder varchar(20)
declare @str0 char(10)

set @str0 = '0000000000'
set @toDate = convert(char(6),getdate(),12) ---currently date,as so 080403

if (rtrim(ltrim(@pType))='1')
begin
     set @pValue = @toDate +left(@str0,@pLeng-7) +  '1'
end

if (rtrim(ltrim(@pType))='0')
begin
    set @pValue = left(@str0,@pLeng-1) +  '1'  
end

----check table is exists or not
if not exists (select [name] from dbo.sysobjects where name= 't_id' and type='U')
begin
 CREATE TABLE [t_id] (
 [idName] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
 [idType] [char] (1) COLLATE Chinese_PRC_CI_AS NULL ,
 [idLength] [int] NULL ,
 [idValue] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
ALTER TABLE [t_id] ADD
 CONSTRAINT [PK_t_id] PRIMARY KEY  CLUSTERED
 (
  [idName]
 )  ON [PRIMARY]  
end

----check the @pName is exists or not
if (not exists (select idName from t_id where idName=@pName))
begin
  insert into t_id (idName,idType,idLength,idValue) values (@pName,@pType,@pLeng,@pValue)
               return  1   ----直接返回
end

----query idvalue
select @pValue=idValue from  t_id where idName=@pName
if (rtrim(ltrim(@pType))='1')
begin
   set @tempDate = left(ltrim(@pValue),6)
   if (@tempDate=@toDate)
        begin
              set @numOrder = convert(varchar(20),convert(int,right(@pValue,@pLeng-len(@toDate)))+1)
     set @pValue =@toDate + left(@str0,@pLeng-6-len(@numOrder)) + ltrim(rtrim(@numOrder))
       end
   else
          begin
                 set @pValue = @toDate +left(@str0,@pLeng-7) +  '1'
         end
    ----update table
end

if (rtrim(ltrim(@pType))='0')
begin
          set @numOrder = convert(varchar(20),convert(int,@pValue)+1)
          set @pValue = left(@str0,@pLeng-len(@numOrder)) +  ltrim(rtrim(@numOrder))
end

    -----update database
    update  t_id set idValue=@pValue where idName=@pName
    return 1
GO