CSDN博客

img ghjhot

我调用的存储过程

发表于2004/9/23 10:39:00  471人阅读

分类: 我的.net

存储过程
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; //返回单号
  }

0 0

相关博文

我的热门文章

img
取 消
img