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">实体</typeparam>
/// <param name="tableName">表名</param>
/// <param name="type">类型</param>
/// <param name="codeName">在表中对应的编码字段</param>
/// <param name="where">验重条件</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);
//检验重复
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;
}
