to 邹健:函数中不允许调用Update ?函数中不允许调用存储过程?那以下功能怎么实现呢?
基础数据如下:
create table Tb_Sequence
(
TableName nvarchar(50) not null,
Sequence int not null
)
insert into Tb_Sequence(TableName,Sequence) values('Tb_DataBackLog',0)
我希望创建一个函数,用来取出下一个Sequence(以后算法可能不是简单的加一这样)值,并记录到表中。
希望不存在并发操作问题。
一定要用函数,以便在普通sql 语句中好使用,这个大约是主键了。
我初步设想如下:
CREATE FUNCTION [dbo].[GetSequence] (@TableName nvarchar(100))
RETURNS int AS
BEGIN
declare @NewSequence int
update tb_sequence set Sequence = Sequence + 1,@NewSequence = Sequence + 1 where TableName like @TableName
return (@NewSequence)
END
但是:Invalid use of 'UPDATE' within a function.
我又尝试:
CREATE PROCEDURE [dbo].[Proc_GetSequence]
@TableName nvarchar(100),
@NewSequence int output
AS
begin
update tb_sequence set Sequence = Sequence + 1,@NewSequence = Sequence + 1 where TableName like @TableName
end
CREATE FUNCTION [dbo].[Func_GetSequence] (@TableName nvarchar(100))
RETURNS int AS
BEGIN
declare @NewSequence int
execute Proc_GetSequence @TableName,@NewSequence = @NewSequence output
return (@NewSequence)
END
select dbo.Func_GetSequence('Tb_DataBackLog')
可是:Only functions and extended stored procedures can be executed from within a function.
我该怎么做呢?