Stored Procedure Create Procedure CreatedJH @ Tablename Nvarchar (40), - Specify your table name @columname nvarchar (50), - Specify one column is ID @ idFormate nvarchar (30), - single number format, such as accessories The purchase list is 'cgd' @ Latestid Char (16) Output - the value returned, that is, the last generated 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)) = 1 Begin Set @ Tempdate = '0' @ Tempdate End Set @ DateStr = Cast (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 - - Get the current last ID number --Create the no if @tempid is null begin - Insert Set @ Tempid = @ IDFORMATE @ datestr '@ Id else Begin Declare @templastForWord As Char (4) Declare @tempcount As int declare @templeneth 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 Returngopublic static string get (String TbName, String Colname, String IDName, SqlConnection CONN) {// Store Procedure Number
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;
