CREATE PROCEDURE [dbo].[SP_FetchNextValue]
@TableName VARCHAR(200),
@Type TINYINT = 1
AS
BEGIN
DECLARE @SQL VARCHAR(MAX)
SELECT @SQL = 'DECLARE @NEXTVALUE INT;SELECT @NEXTVALUE = NEXT VALUE FOR DBO.SEQ_'+TableName+'_'++LTRIM(Type)+';SELECT '''+Prefix+'''+RIGHT(''0000000000''+LTRIM(@NEXTVALUE),'+LTRIM(MiddleNumber)+') NextValue;'
FROM DBO.T_Core_SequenceMapping
WHERE TableName = @TableName
AND Type = @Type
EXEC(@SQL)
END
GO
C#或者表自增主鍵值
/// <typeparam name="TEntity">實(shí)體</typeparam>
/// <param name="tableName">表名</param>
/// <param name="type">類(lèi)型</param>
/// <param name="codeName">在表中對(duì)應(yīng)的編碼字段</param>
/// <param name="where">驗(yàn)重條件</param>
/// <returns>編碼</returns>
public string GetCode<TEntity>(string tableName,int type,string codeName="Code",string query = "1=1") where TEntity : class
{
var newCode = "";
string mysql = string.Format("EXEC DBO.SP_FetchNextValue '{0}',{1}", tableName, type);
while (string.IsNullOrEmpty(newCode))
{
//獲取編碼
newCode = new RhisRepository().GetObjectByQuery<string>(mysql);
//檢驗(yàn)重復(fù)
string mysqls = string.Format("select * from {0} where {1}='{2}' and {3}", tableName, codeName, newCode,query);
var list = new RhisRepository().GetListByQuery<TEntity>(mysqls);
if (list != null && list.Count > 0)
{
newCode = "";
}
}
return newCode;
}
