DECLARE @whereB NVARCHAR(1000),@aa nvarchar(2000),@bb nvarchar(2000)
declare abc cursor for select company_name,usertelno,iswuliu,isnull(jm_ts,0) as jm_ts from V_CallInUser_Table
open abc
fetch abc into @company_name,@usertelno,@iswuliu,@jm_ts
delete from xd_smsvoice_sum_T
while @@fetch_status=0
begin
set nocount on
if @jm_ts is null
set @jm_ts=0
set @sms_price2=@jm_ts*0.10
set @sql2=N'select @b=sum(price) from V_CallInUser_GJDX_Msgbill '+@whereB
set @whereB=N'where usertelno='+''''+@usertelno+''''+@aa+' group by usertelno '
set @aa=N' and sendtime>='+''''+@TheStarttime+''''+' and sendtime<='+''''+@TheEndTime+''''
set @Parm2=N'@b money output'
exec sp_executesql @sql2,@Parm2, @b=@sms_price output //输出的这个值是空呢,为什么?不对吗这样写?sendtime是时间if @sms_price is null
set @sms_price=0
if @voice_price is null
set @voice_price=0
if @sms_price2 is null
set @sms_price2=0
set @sum_price=isnull(@sms_price,0)-isnull(@sms_price2,0)
if @sum_price<=0
set @sum_price=0
set nocount Offinsert into xd_smsvoice_sum_T (iswuliu,company_name,usertelno,sms_price,sms_price2,voice_price,sum_price) values(@iswuliu,@company_name,@usertelno,@sms_price,@sms_price2,@voice_price,@sum_price)
fetch abc into @company_name,@usertelno,@iswuliu,@jm_ts
end
select * from xd_smsvoice_sum_T
close abc
deallocate abc
如题:@sms_price为什么是空值呢?
set @whereB=N'where usertelno='+''''+@usertelno+''''+@aa+' group by usertelno '
set @aa=N' and sendtime>='+''''+@TheStarttime+''''+' and sendtime<='+''''+@TheEndTime+''''
set @Parm2=N'@b money output'
declare @sms_price money
exec sp_executesql @sql2,@Parm2, @b=@sms_price output //输出的这个值是空呢,为什么?不对吗这样写?sendtime是时间
#1. 对于执行存储过程:exec sp_executesql @sql2, @Parm2, @b=@sms_price output, 参数列表和参数值会一一对应, 即@Parm2中的@b output对应的是@sms_price output. 虽然第一个参数@b=@sms_price output表达式的语法没有错误,但在sp_executesql中并不会把@sms_price的值赋给@b.
#2. 看一下联机帮助对sp_executesql语法的解释
declare @sql2 Nvarchar(100)
declare @Parm2 Nvarchar(100)
declare @b Nvarchar(100)
declare @sms_price Nvarchar(100)set @sql2 = N'select @b = N''test'''
set @Parm2 = N'@b Nvarchar(100) output'
set @sms_price = N'abc'
--set @b = @sms_priceexec sp_executesql @sql2, @Parm2, @b=@sms_price outputselect @b
select @sms_price
create table V_CallInUser_Table(company_name nvarchar(20),usertelno nvarchar(20),iswuliu int,jm_ts int)
insert into V_CallInUser_Table select 'AAAA','10521245245',1,158
insert into V_CallInUser_Table select 'BBBB','11654656445',1,369
create table V_CallInUser_GJDX_Msgbill(price decimal(18,2),usertelno nvarchar(20),sendtime datetime)
insert into V_CallInUser_GJDX_Msgbill select 152.54,'10521245245','2010-11-15'
insert into V_CallInUser_GJDX_Msgbill select 382.25,'10521245245','2010-11-18'
insert into V_CallInUser_GJDX_Msgbill select 279.11,'10521245245','2010-11-20'
insert into V_CallInUser_GJDX_Msgbill select 220.20,'11654656445','2010-11-18'
insert into V_CallInUser_GJDX_Msgbill select 777.77,'11654656445','2010-11-20'
create table xd_smsvoice_sum_T (iswuliu int,company_name nvarchar(20),usertelno nvarchar(20),sms_price decimal(18,2),sms_price2 decimal(18,2),voice_price decimal(18,2),sum_price decimal(18,2))
go
DECLARE @whereB NVARCHAR(1000),@aa nvarchar(2000),@bb nvarchar(2000)
declare @company_name nvarchar(20),@usertelno nvarchar(20),@iswuliu int,@jm_ts int
declare @sms_price2 decimal(18,2),@sql2 nvarchar(4000),@TheStarttime nvarchar(20),@TheEndTime nvarchar(20),@Parm2 nvarchar(1000),@sms_price decimal(18,2),@voice_price decimal(18,2),@sum_price decimal(18,2)
set @TheStarttime='2010-11-01'
set @TheEndTime='2010-11-30'
declare abc cursor for select company_name,usertelno,iswuliu,isnull(jm_ts,0) as jm_ts from V_CallInUser_Table
open abc
fetch abc into @company_name,@usertelno,@iswuliu,@jm_ts
delete from xd_smsvoice_sum_T
while @@fetch_status=0
begin
set nocount on
if @jm_ts is null
set @jm_ts=0
set @sms_price2=@jm_ts*0.10
set @Parm2=N'@b money output'
set @aa=N' and sendtime>='+''''+@TheStarttime+''''+' and sendtime<='+''''+@TheEndTime+''''
set @whereB=N'where usertelno='+''''+@usertelno+''''+@aa+' group by usertelno '
set @sql2=N'select @b=sum(price) from V_CallInUser_GJDX_Msgbill '+@whereB
exec sp_executesql @sql2,@Parm2, @b=@sms_price output --输出的这个值是空呢,为什么?不对吗这样写?sendtime是时间if @sms_price is null
set @sms_price=0
if @voice_price is null
set @voice_price=0
if @sms_price2 is null
set @sms_price2=0
set @sum_price=isnull(@sms_price,0)-isnull(@sms_price2,0)
if @sum_price<=0
set @sum_price=0
set nocount Offinsert into xd_smsvoice_sum_T (iswuliu,company_name,usertelno,sms_price,sms_price2,voice_price,sum_price) values(@iswuliu,@company_name,@usertelno,@sms_price,@sms_price2,@voice_price,@sum_price)
fetch abc into @company_name,@usertelno,@iswuliu,@jm_ts
end
select * from xd_smsvoice_sum_T
close abc
deallocate abcgo
drop table V_CallInUser_Table,V_CallInUser_GJDX_Msgbill,xd_smsvoice_sum_T
/*
iswuliu company_name usertelno sms_price sms_price2 voice_price sum_price
----------- -------------------- -------------------- --------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------
1 AAAA 10521245245 813.90 15.80 0.00 798.10
1 BBBB 11654656445 997.97 36.90 0.00 961.07(2 行受影响)*/
set @whereB=N'where usertelno='+''''+@usertelno+''''+@aa+' group by usertelno '
set @aa=N' and sendtime>='+''''+@TheStarttime+''''+' and sendtime<='+''''+@TheEndTime+''''
set @Parm2=N'@b money output'
这里的顺序反了吧,你可以用PRINT把最后组合成的SQL语句打印出来先看下是否符合你所要求的。
另外@TheStarttime,@TheEndTime这2个在哪里定义过?
最后我把它置0了就好了!