编号累加得函数:
CREATE FUNCTION f_tbSysMenuChildNextBH()
RETURNS char(10)
AS
BEGIN
RETURN(SELECT rtrim('BH' + RIGHT(1000001+ISNULL(RIGHT(MAX(menuChldId),6),0),6)) FROM tbSysMenuChild WITH(XLOCK,PAGLOCK))
END
-----------------------------------
怎么把这个函数改成通用得,可以传表名、编号字段名参数,实现在每一个表中都能调用这个函数
CREATE FUNCTION f_tbSysMenuChildNextBH()
RETURNS char(10)
AS
BEGIN
RETURN(SELECT rtrim('BH' + RIGHT(1000001+ISNULL(RIGHT(MAX(menuChldId),6),0),6)) FROM tbSysMenuChild WITH(XLOCK,PAGLOCK))
END
-----------------------------------
怎么把这个函数改成通用得,可以传表名、编号字段名参数,实现在每一个表中都能调用这个函数
@STRING VARCHAR(10),
@TABLENAME VARCHAR(20),
@COLUMNNAME VARCHAR(20),
@OUTPUTE VARCHAR(200) OUTPUT
AS
BEGIN
DECLARE @SQL NVARCHAR(2000)
SET @SQL='SELECT @OUTPUTE=rtrim('''+@STRING+''' + RIGHT(1000001+ISNULL(RIGHT(MAX('+@COLUMNNAME+'),6),0),6)) FROM '+@TABLENAME+' WITH(XLOCK,PAGLOCK)'
PRINT @SQL
EXEC SP_EXECUTESQL @SQL,N'@OUTPUTE VARCHAR(20) OUTPUT',@OUTPUTE OUTPUT
END---测试
DECLARE @OUT VARCHAR(200)
EXEC P_tbSysMenuChildNextBH 'BH','表名','字段',@OUT OUTPUT
SELECT @OUT