SQL语句查询有时有返回值,有时没有返回值(实际有内容,但没有返回),在数据库中直接调用存储,能查出来,报错,贴出来,大家看下什么原因,另:return_value=-6是怎么回事
代码如下:
- SQL code
--查询清算明细 张凤仪 2012-1-13USE [L2SettleDB]GO/****** Object: StoredProcedure [dbo].[reader_proc] Script Date: 01/13/2012 13:53:56 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO --drop procedure [dbo].[reader_proc] --删除存储过程create procedure [dbo].[reader_proc](@dt datetime, --起始日期@day int, --天数@mcht varchar(15), --商户号@sett varchar(8), --清算号@term_id varchar(1000), --子门店号@CurrPage int, --当前页码@PageSize int, --每页记录数@raing_sum float output, --@PER_sum float output, --@raing int output, --@RSA_sum float output, --@recordcount int output --记录总数)asdeclare @settle_mode varchar(1) --清算模式declare @mcht_role_type varchar(3) --商户角色类型declare @da varchar(10) --日期字符串declare @da1 varchar(10) --declare @sett1 varchar(50) --存储清算号条件declare @sql varchar(max) --拼接SQL字符串declare @dt1 varchar(10) --起始日期declare @i int --循环控制条件declare @startdate datetime --起始时间declare @enddate datetime --结束时间set @i=0 --循环控制条件初始值set @[email protected]set @startdate=GETDATE() --记录开始时间while (@i<[email protected]) begin set @dt=DATEADD(DAY,@i,@dt1) --set @da=CONVERT(char(8),current_timestamp,101) set @da=CONVERT(char(8), @dt,112) if left(@sett,2)<>'96' and len(@sett)>0 set @sett1=' and term_id=''' + @sett +'''' else set @sett1='' if @term_id='a' begin if @i=0 --拼SQL语句 set @sql='select * from [L2SettleDB].[dbo].[[email protected]+'] where [email protected]+'''' [email protected] else set @[email protected] + ' union all select * from [L2SettleDB].[dbo].[[email protected]+'] where [email protected]+'''' [email protected] end else begin if @i=0 --拼SQL语句 set @sql='select * from [L2SettleDB].[dbo].[[email protected]+'] where [email protected]+''' and term_id in (' + @term_id + ')' else set @[email protected] + ' union all select * from [L2SettleDB].[dbo].[[email protected]+'] where [email protected]+''' and term_id in (' + @term_id + ')' end select @[email protected]+1 if @i>@day breakendif OBJECT_ID('[tempdb].[dbo].#t') is not null --判断临时表是否存在，存在则删除 drop table #tselect * into #t from [L2SettleDB].[dbo].[L2_L20110101] where 1=2insert into #t exec(@sql)select @settle_mode=(select sett_mode from [L2SettleDB].[dbo].[l2_mcht] where [email protected])select @mcht_role_type=(select mcht_role_type from [L2SettleDB].[dbo].[l2_mcht] where [email protected])select @raing_sum=(select sum(tranamount) from #t)if @mcht_role_type='101' --统计汇总 select @PER_sum=(select SUM(acq_mcht_fee_value) from #t)else if @mcht_role_type='110' select @PER_sum=(select SUM(iss_mcht_fee_value) from #t) else select @PER_sum=(select SUM(agent_mcht_fee_value) from #t) if @settle_mode='1' begin if @mcht_role_type='101' begin if @sett<>'' begin select @RSA_sum=(select sum(per_trans_amt_successful) from [L2SettleDB].[dbo].[l2_sum_transaction] where acq_mcht_id= + @mcht + ' and acq_term_id= ' + @sett ) select @raing=(select sum(per_trans_num_successful) from [L2SettleDB].[dbo].[l2_sum_transaction] where acq_mcht_id= + @mcht + ' and acq_term_id= ' + @sett ) end else begin select @RSA_sum=(select sum(per_trans_amt_successful) from [L2SettleDB].[dbo].[l2_sum_transaction] where acq_mcht_id= + @mcht) select @raing=(select sum(per_trans_num_successful) from [L2SettleDB].[dbo].[l2_sum_transaction] where acq_mcht_id= + @mcht) end end else if @mcht_role_type='110' begin select @RSA_sum=(select sum(per_trans_amt_successful) from [L2SettleDB].[dbo].[l2_sum_transaction] where iss_mcht_id= + @mcht) select @raing=(select sum(per_trans_num_successful) from [L2SettleDB].[dbo].[l2_sum_transaction] where iss_mcht_id= + @mcht) end else begin select @RSA_sum=(select sum(per_trans_amt_successful) from [L2SettleDB].[dbo].[l2_sum_transaction] where iss_mcht_id= + @mcht) select @raing=(select sum(per_trans_num_successful) from [L2SettleDB].[dbo].[l2_sum_transaction] where iss_mcht_id= + @mcht) end endelse --不等于1 begin if @mcht_role_type='101' begin if @sett<>'' begin select @RSA_sum=(select sum(pnt_trans_amt_successful) from [L2SettleDB].[dbo].[l2_sum_transaction] where acq_mcht_id= + @mcht + ' and acq_term_id= ' + @sett ) select @raing=(select sum(pnt_trans_num_successful) from [L2SettleDB].[dbo].[l2_sum_transaction] where acq_mcht_id= + @mcht + ' and acq_term_id= ' + @sett ) end else begin select @RSA_sum=(select sum(pnt_trans_amt_successful) from [L2SettleDB].[dbo].[l2_sum_transaction] where acq_mcht_id= + @mcht) select @raing=(select sum(pnt_trans_num_successful) from [L2SettleDB].[dbo].[l2_sum_transaction] where acq_mcht_id= + @mcht) end end else if @mcht_role_type='110' begin select @RSA_sum=(select sum(pnt_trans_amt_successful) from [L2SettleDB].[dbo].[l2_sum_transaction] where iss_mcht_id= + @mcht) select @raing=(select sum(pnt_trans_num_successful) from [L2SettleDB].[dbo].[l2_sum_transaction] where iss_mcht_id= + @mcht) end else begin select @RSA_sum=(select sum(pnt_trans_amt_successful) from [L2SettleDB].[dbo].[l2_sum_transaction] where iss_mcht_id= + @mcht) select @raing=(select sum(pnt_trans_num_successful) from [L2SettleDB].[dbo].[l2_sum_transaction] where iss_mcht_id= + @mcht) end end set @recordcount=(select COUNT(*) from #t) select * from (select *,row_number() over(ORDER BY trandate) as num from #t) aa where num between (@CurrPage-1)[email protected]+1 and @[email protected] --返回指定记录 set @enddate=GETDATE() --记录结束时间--DATEDIFF(MS,@startdate,@enddate) --(select CONVERT(varchar(12),@startdate,114))+':'+(select CONVERT(varchar(12),@enddate,114)) 记录查询时间update [L2SettleDB].[dbo].[L2] set [email protected] --DATEDIFF(MS,@startdate,@enddate) --(select CONVERT(varchar(12),@startdate,114))+':'+(select CONVERT(varchar(12),@enddate,114)) --str(@PER_sum,10) +' ' [email protected]_role_type + ' ' + 临时记录在表