存储过程
CREATE PROCEDURE CreateDJH
@tableName NVARCHAR(40), --指定你的表名
@columName NVARCHAR(50),--指定一个那一列是ID
@idFormate NVARCHAR(30),--单号的格式,如配件采购单为'CGD'
@latestID CHAR(16) OUTPUT --返回的值 ,也就是最后生成的ID
AS
DECLARE @tempID AS NVARCHAR(16)
DECLARE @sqlStr AS NVARCHAR(254)
DECLARE @dateStr AS CHAR(8)
DECLARE @tempdate AS CHAR(2)
--GET THE DATE STR
SET @tempdate=CAST(DATEPART(dd,GETDATE() ) AS CHAR(2))
IF LEN(@tempdate)=1
BEGIN
SET @tempdate='0'+@tempdate
END
SET @dateStr=CAST(DATEPART(yy, GETDATE()) AS CHAR(4) )
+SUBSTRING( DATENAME(mm, getdate()), 1, 3) +@tempdate
--GET THE LAST NO
SET @sqlStr=N'SELECT TOP 1 @ID = '+@columName+' FROM ' +@tableName+' WHERE '+@columName+' LIKE ltrim(rtrim(@likeStr)) ORDER BY '+@columName+' DESC'
DECLARE @templikeStr AS CHAR(14)
SET @templikeStr='%'+@idFormate+ @dateStr+'%'
EXECUTE sp_executesql @sqlStr,N' @likeStr CHAR(14) ,@ID CHAR(16) OUTPUT', @templikeStr,@tempID OUTPUT --得到当前最后的一个ID号
--CREATE THE NO
IF @tempID IS NULL
BEGIN --第一次插入
SET @tempID=@idFormate+ @dateStr+'0001'
END
ELSE
BEGIN
DECLARE @tempLastForWord AS CHAR(4)
DECLARE @tempCount AS INT
DECLARE @tempLenth AS INT
SET @tempLastForWord=RIGHT(@tempID,4)
SET @tempCount=CAST(@tempLastForWord AS INT)
SET @tempCount=@tempCount+1
SET @tempLastForWord=CAST(@tempCount AS CHAR(4))
SET @tempLenth=LEN(@tempLastForWord)
IF @tempLenth=1
BEGIN
SET @tempLastForWord='000'+@tempLastForWord
END
ELSE IF @tempLenth=2
BEGIN
SET @tempLastForWord='00'+@tempLastForWord
END
ELSE IF @tempLenth=3
BEGIN
SET @tempLastForWord='0'+@tempLastForWord
END
SET @tempID=@idFormate+ @dateStr+@tempLastForWord
END
SELECT @latestID=@tempID
RETURN
GO
public static string getID(string tbName,string colName,string idName,SqlConnection conn)
{
//存储过程 取得单号
SqlCommand cmdGetID=new SqlCommand();
cmdGetID.CommandType=CommandType.StoredProcedure;
cmdGetID.Connection=conn;
cmdGetID.CommandText="CreateDJH";
SqlParameter un=new SqlParameter();
un.ParameterName="@tableName";
un.Direction=ParameterDirection.Input;
un.SourceVersion=DataRowVersion.Current;
cmdGetID.Parameters.Add(un);
cmdGetID.Parameters["@tableName"].Value=tbName;
SqlParameter col=new SqlParameter();
col.ParameterName="@columName";
col.Direction=ParameterDirection.Input;
col.SourceVersion=DataRowVersion.Current;
cmdGetID.Parameters.Add(col);
cmdGetID.Parameters["@columName"].Value=colName;
SqlParameter idFormat=new SqlParameter();
idFormat.ParameterName="@idFormate";
idFormat.Direction=ParameterDirection.Input;
idFormat.SourceVersion=DataRowVersion.Current;
cmdGetID.Parameters.Add(idFormat);
cmdGetID.Parameters["@idFormate"].Value=idName;
//输出参数
SqlParameter latestID=new SqlParameter();
latestID=new SqlParameter("@latestID",SqlDbType.Char,15);
latestID.Direction=ParameterDirection.Output;
cmdGetID.Parameters.Add(latestID);
cmdGetID.ExecuteNonQuery();
string RID=cmdGetID.Parameters["@latestID"].Value.ToString();
return RID; //返回单号
}