create procedure draw
@itcode NVARCHAR(50) output
as
declare
@err varchar(10),
@id intselect * from ticketLog where itcode = @itcode--//就是这个查询,第一次运行的时候,返回一条空结果,我不想要
if @@ROWCOUNT = 0
select top 1 @id=id from ticket where allSum>0 order by newid()
if @@ROWCOUNT = 0
begin
set @err='notTicket'
return (1)
end
else
begin
update ticket set allSum=allSum-1 where id=@id
insert into ticketLog (itcode,ticketID,dateTime) values(@itcode,@id,GETDATE())
select * from ticket where id = @id--//这个查询是我想要的
end
if @@ROWCOUNT > 0
begin
set @err='repeat'
return (1)
end
godraw 'zbai'==============
另外,如何把 @err也输出出来呢?我是返回给asp的
@itcode NVARCHAR(50) output
as
declare
@err varchar(10),
@id intselect * from ticketLog where itcode = @itcode--//就是这个查询,第一次运行的时候,返回一条空结果,我不想要
if @@ROWCOUNT = 0
select top 1 @id=id from ticket where allSum>0 order by newid()
if @@ROWCOUNT = 0
begin
set @err='notTicket'
return (1)
end
else
begin
update ticket set allSum=allSum-1 where id=@id
insert into ticketLog (itcode,ticketID,dateTime) values(@itcode,@id,GETDATE())
select * from ticket where id = @id--//这个查询是我想要的
end
if @@ROWCOUNT > 0
begin
set @err='repeat'
return (1)
end
godraw 'zbai'==============
另外,如何把 @err也输出出来呢?我是返回给asp的
@itcode NVARCHAR(50)-- 这个是输入参数
@err varchar(10) output -->这个才是output参数
as
declare @id intselect * from ticketLog where itcode = @itcode--//就是这个查询,第一次运行的时候,返回一条空结果,我不想要
if @@ROWCOUNT = 0
begin
select top 1 @id=id from ticket where allSum>0 order by newid()
if @@ROWCOUNT = 0
begin
set @err='notTicket'
return (1)
end
else
begin
update ticket set allSum=allSum-1 where id=@id
insert into ticketLog (itcode,ticketID,dateTime) values(@itcode,@id,GETDATE())
select * from ticket where id = @id--//这个查询是我想要的
end
endelse
begin
set @err='repeat'
return (1)
end
godeclare @err varchar(10)
exec draw 'zbai', @err output
@itcode NVARCHAR(50)-- 这个是输入参数
@err varchar(10) output -->这个才是output参数
as
declare @id intselect * from ticketLog where itcode = @itcode
if @@ROWCOUNT = 0
begin-->这里需要begin
select top 1 @id=id from ticket where allSum>0 order by newid()
if @@ROWCOUNT = 0
begin
set @err='notTicket'
return (1)
end
else
begin
update ticket set allSum=allSum-1 where id=@id
insert into ticketLog (itcode,ticketID,dateTime) values(@itcode,@id,GETDATE())
select * from ticket where id = @id
end
end-->这里需要endelse -->这里不能if @@ROWCOUNT <> 0,要用else,因为@@ROWCOUNT是返回受上一个语句影响的行数
begin
set @err='repeat'
return (1)
end
godeclare @err varchar(10) -->定义变量接收output参数@err
exec draw 'zbai', @err output
select @err
@itcode NVARCHAR(50)-- 这个是输入参数
@err varchar(10) output -->这个才是output参数
as
declare @id int-->如果没有这条记录
if not exists (select * from ticketLog where itcode = @itcode)
begin
select top 1 @id=id from ticket where allSum>0 order by newid()
if @id is null --> 这里用@id代替@@rowcount
begin
set @err='notTicket'
return (1)
end
else
begin
update ticket set allSum=allSum-1 where id=@id
insert into ticketLog (itcode,ticketID,dateTime) values(@itcode,@id,GETDATE())
select * from ticket where id = @id--//这个查询是我想要的
end
end-->否则就有这条记录
else
begin
select * from ticketLog where itcode = @itcode
set @err='repeat'
return (1)
end
godeclare @err varchar(10)
exec draw 'zbai', @err output
select @err
ado recordset
在客户端,用adocmd执行存储过程,查询的结果返回到recordset
为第一个结果集,
再创建一空recordset
给此空recordset赋值=第一个结果集.nextrecordset用此方法返回 第二个结果集,就是你想要的