在查询分析器显示储存过程的返回值
use OC
if exists( select name from sysobjects where name='proc_dailyRecord_GetSerialNo' and type='P')
drop procedure proc_dailyRecord_GetSerialNo
GO
create procedure proc_dailyRecord_GetSerialNo
@caseType varchar(5),
@caseResult varchar(15)
as
declare
@serialNo int,
@inputDate datetime,
@count int
select @count=count(*) from serial_number
if @count=0
begin
insert  into serial_number  (BM_JT_DATE,BM_JT) values (getdate(),0)
end
if @caseType='BM'
if @caseResult='Complete'
begin
select @serialNo = BM_CO, @inputDate = BM_CO_DATE from serial_number
if DATEDIFF(day,@inputDate,getdate())=0
begin
update serial_number set BM_CO=@serialNo+1,BM_CO_DATE=getdate()
return @serialNo+1
end
else
begin
update serial_number set BM_CO=1,BM_CO_DATE=getdate()
return 1
end
end
else if @caseResult='Joint Test'
begin
select @serialNo = BM_JT, @inputDate = BM_JT_DATE from serial_number
if DATEDIFF(day,@inputDate,getdate())=0
begin
update serial_number set BM_JT=@serialNo+1,BM_JT_DATE=getdate()
return @serialNo+1
end
else
begin
update serial_number set BM_JT=1,BM_JT_DATE=getdate()
return 1
end
end
else if @caseResult='Re-Date'
begin
select @serialNo = BM_RD, @inputDate = BM_RD_DATE from serial_number
if DATEDIFF(day,@inputDate,getdate())=0
begin
update serial_number set BM_RD=@serialNo+1,BM_RD_DATE=getdate()
return @serialNo+1
end
else
begin
update serial_number set BM_RD=1,BM_RD_DATE=getdate()
return 1
end
end
else if @caseResult='N/A'
begin
select @serialNo = BM_NA, @inputDate = BM_NA_DATE from serial_number
if DATEDIFF(day,@inputDate,getdate())=0
begin
update serial_number set BM_NA=@serialNo+1,BM_NA_DATE=getdate()
return @serialNo+1
end
else
begin
update serial_number set BM_NA=1,BM_NA_DATE=getdate()
return 1
end
end
else if @caseResult='Notebook Test,Customer Accept'
begin
select @serialNo = BM_NBA, @inputDate = BM_NBA_DATE from serial_number
if DATEDIFF(day,@inputDate,getdate())=0
begin
update serial_number set BM_NBA=@serialNo+1,BM_NBA_DATE=getdate()
return @serialNo+1
end
else
begin
update serial_number set BM_NBA=1,BM_NBA_DATE=getdate()
return 1
end
end
else if @caseResult='Notebook Test,Customer Not Accept'
begin
select @serialNo = BM_NBN, @inputDate = BM_NBN_DATE from serial_number
if DATEDIFF(day,@inputDate,getdate())=0
begin
update serial_number set BM_NBN=@serialNo+1,BM_NBN_DATE=getdate()
return @serialNo+1
end
else
begin
update serial_number set BM_NBN=1,BM_NBN_DATE=getdate()
return 1
end
end
else
if @caseResult='Complete'
begin
select @serialNo = VM_CO, @inputDate = VM_CO_DATE from serial_number
if DATEDIFF(day,@inputDate,getdate())=0
begin
update serial_number set VM_CO=@serialNo+1,VM_CO_DATE=getdate()
return @serialNo+1
end
else
begin
update serial_number set VM_CO=1,VM_CO_DATE=getdate()
return 1
end
end
else if @caseResult='Joint Test'
begin
select @serialNo = VM_JT, @inputDate = VM_JT_DATE from serial_number
if DATEDIFF(day,@inputDate,getdate())=0
begin
update serial_number set VM_JT=@serialNo+1,VM_JT_DATE=getdate()
return @serialNo+1
end
else