create function GetNewMemberId()
returns int
as
begin
declare @tmp int
select @tmp=max(idtype) from IdGenerator return isnull(@tmp+1,1)
end
go
create function GetNewProductId()
returns int
as
begin
declare @tmp int
select @tmp=max(idvalue) from IdGenerator return isnull(@tmp+1,1)
end
go
returns int
as
begin
declare @tmp int
select @tmp=max(idtype) from IdGenerator return isnull(@tmp+1,1)
end
go
create function GetNewProductId()
returns int
as
begin
declare @tmp int
select @tmp=max(idvalue) from IdGenerator return isnull(@tmp+1,1)
end
go
returns int
as
begin
declare @tmp int
select @tmp=max(idtype) from IdGenerator with(rowlock) return isnull(@tmp+1,1)
end
go
create function GetNewProductId()
returns int
as
begin
declare @tmp int
select @tmp=max(idvalue) from IdGenerator with(rowlock) return isnull(@tmp+1,1)
end
go
还有一个是在Select时函数加了行锁,在何时解锁的呢? 在函数执行完毕的时候自动解锁的吗?
returns int
as
begin
declare @tmp int
select @tmp=max(idtype) from IdGenerator with(rowlock)
where idtype='MemberId' return isnull(@tmp+1,1)
end
go
create function GetNewProductId()
returns int
as
begin
declare @tmp int
select @tmp=max(idvalue) from IdGenerator with(rowlock)
where idtype='ProductId' return isnull(@tmp+1,1)
end
go
我不知道SQL函数内是不能使用update的,那我原来的思路就有问题了我原本的思路是
列 idtype idvalue
记录 'memberid' 1
每调用一次GetNewMemberId()函数, idvalue的值就增加1
不过这样做的话就要用到Update语句了。请问vivianfdlpw() 一下,你给的代码里是如何确保函数返回值是不断增长的呢?
我好像没有看到改变idvalue的代码?另外想再问一下, 在函数内是无法执行Update操作的,那能不能执行 Insert? Delete? 事务?
returns int
as begin
declare @id int
begin tran
update IdGenerator
set @id=idvalue =idvalue +1
where idtype ='MemberId'
if @@rowcount<>1 or @@error<>0
begin
rollback tran
raiserror( '更新失败' ,16,1)
end
else
commit tran
return @id
end --------------------------------
未考虑没有记录时情况,
returns int
as
begin
declare @tmp int
select @tmp=max(idvalue) from IdGenerator with(rowlock)
where idtype='ProductId' return isnull(@tmp+1,1)
end
go
@MemberId int output
asupdate Test with (Rowlock)set idvalue=idvalue+1,
@MemberId=idvalue
where idtype='MemberId'Go
/* 执行存储过程,在程序里边就可以获得存储过程返回的值 */declare @a int
exec dbo.GetNewMemberId @a output
select @a