if exists(select name from sysobjects where name='GetRecord' and type = 'p') drop procedure GetRecordGO
create procedure GetRecord@id int output, --输出p_id和p_path@path nvarchar(255) output,@pagenum int output,@serverid nvarchar(50) --输入serveridas
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; endif(@path is NULL)begin set @path = 'empty';end
if @@error=0 print 'Good'elseprint 'Fail'GO
--测试程序declare @idd intdeclare @ppath nvarchar(255)declare @ppage intEXEC dbo.GetRecord @idd output,@ppath output,@ppage output, '0'select '1'=@idd, '2'=@ppath, '3'=@ppagego
sql server 中，按CTR+0，即可输入空值NULL
