if exists(select name from sysobjects where name='GetRecord' and type = 'p')
drop procedure GetRecord
GO
create procedure GetRecord
@id int output, --输出p_id和p_path
@path nvarchar(255) output,
@pagenum int output,
@serverid nvarchar(50) --输入serverid
as
if(@serverid = '') --若serverid为空,则不作为查询条件
select top 1 @id = p_id, @path = p_path, @pagenum = p_pages from n_project where p_flag = '0';
else
select @id = p_id, @path = p_path, @pagenum = p_pages from n_project where p_flag = '0' and p_serverid = @serverid;
--waitfor delay '00:00:10' --等待10秒,测试时使用
if(@id > 0)
begin
Update n_project set p_flag = '1', p_stime = GetDate() where p_id = @id and p_flag = '0' --GetDate()可获得系统时间
if @@rowcount = 0 --若更新条数为0,则认为发生冲突,将@id置0,按空记录处理。这是为了防止多个程序同时查询更新,以此来进行互斥
set @id = 0;
end
else
begin
set @id = 0; --若没有结果则给个默认值,否则直接返回NULL会使程序错误
set @path = 'empty'; --若p_path为NULL,则它也会返回NULL,从而造成程序错误
set @pagenum = 0;
end
if(@path is NULL)
begin
set @path = 'empty';
end
if @@error=0
print 'Good'
else
print 'Fail'
GO
--测试程序
declare @idd int
declare @ppath nvarchar(255)
declare @ppage int
EXEC dbo.GetRecord @idd output,@ppath output,@ppage output, '0'
select '1'=@idd, '2'=@ppath, '3'=@ppage
go
sql server 中,按CTR+0,即可输入空值NULL