declare @ZScore_ID nvarchar(255),
@allZScore_ID nvarchar(255),
@ZGift_ID nvarchar(255),
@allZGift_ID nvarchar(255),
@sumqtyvip nvarchar(255),
@sumqtysvip nvarchar(255),
@sumqtyallvip nvarchar(255),
@ZVIP_Class nvarchar(255),
@Gift nvarchar(255),
@data nvarchar(255)
set @ZVIP_Class='全部'
set @Gift='全部'
set @data='2004-01-09'select @ZScore_ID=ZScore_ID from ZSMemberInfo where ZVIP_Class=@ZVIP_Class)select @allZScore_ID=ZScore_ID from ZSMemberInfoselect @ZGift_ID=ZGift_ID from ZSGiftInfo where ZGift_Desc=@Giftselect @allZGift_ID=ZGift_ID from ZSGiftInfo select @sumqtyvip=sum(zqty) from ZSGiftSignIn_d,ZSGiftSignIn_m where ZSGiftSignIn_d.ZGift_ID in (select ZGift_ID from ZSGiftInfo where zGift_Category='普通卡')and ZSGiftSignIn_m.ZVouch_ID=ZSGiftSignIn_d.ZVouch_ID and ZSGiftSignIn_d.ZScore_ID in (case when @ZVIP_Class='全部' then @allZScore_ID else @ZScore_ID end ) and ZSGiftSignIn_m.ZStart_Date = @data and ZSGiftSignIn_d.ZGift_ID in ( case when @Gift='全部' then @allZGift_ID else @ZGift_ID end)
select @sumqtysvip=sum(zqty) from ZSGiftSignIn_d,ZSGiftSignIn_m where ZSGiftSignIn_d.ZGift_ID in (select ZGift_ID from ZSGiftInfo where zGift_Category='白金卡')and ZSGiftSignIn_m.ZVouch_ID=ZSGiftSignIn_d.ZVouch_ID and ZSGiftSignIn_d.ZScore_ID in (case when @ZVIP_Class='全部' then @allZScore_ID else @ZScore_ID end )and ZSGiftSignIn_m.ZStart_Date = @data and ZSGiftSignIn_d.ZGift_ID in (case when @Gift='全部' then @allZGift_ID else @ZGift_ID end)
select @sumqtyallvip=sum(zqty) from ZSGiftSignIn_d,ZSGiftSignIn_m where ZSGiftSignIn_d.ZGift_ID in (select ZGift_ID from ZSGiftInfo where zGift_Category not in ('普通卡','白金卡'))and ZSGiftSignIn_m.ZVouch_ID=ZSGiftSignIn_d.ZVouch_ID and ZSGiftSignIn_d.ZScore_ID in (case when @ZVIP_Class='全部' then @allZScore_ID else @ZScore_ID end) and ZSGiftSignIn_m.ZStart_Date = @data and ZSGiftSignIn_d.ZGift_ID in (case when @Gift='全部' then @allZGift_ID else @ZGift_ID end) select @sumqtyvip,@sumqtysvip,@sumqtyallvip
GO
@allZScore_ID nvarchar(255),
@ZGift_ID nvarchar(255),
@allZGift_ID nvarchar(255),
@sumqtyvip nvarchar(255),
@sumqtysvip nvarchar(255),
@sumqtyallvip nvarchar(255),
@ZVIP_Class nvarchar(255),
@Gift nvarchar(255),
@data nvarchar(255)
set @ZVIP_Class='全部'
set @Gift='全部'
set @data='2004-01-09'select @ZScore_ID=ZScore_ID from ZSMemberInfo where ZVIP_Class=@ZVIP_Class)select @allZScore_ID=ZScore_ID from ZSMemberInfoselect @ZGift_ID=ZGift_ID from ZSGiftInfo where ZGift_Desc=@Giftselect @allZGift_ID=ZGift_ID from ZSGiftInfo select @sumqtyvip=sum(zqty) from ZSGiftSignIn_d,ZSGiftSignIn_m where ZSGiftSignIn_d.ZGift_ID in (select ZGift_ID from ZSGiftInfo where zGift_Category='普通卡')and ZSGiftSignIn_m.ZVouch_ID=ZSGiftSignIn_d.ZVouch_ID and ZSGiftSignIn_d.ZScore_ID in (case when @ZVIP_Class='全部' then @allZScore_ID else @ZScore_ID end ) and ZSGiftSignIn_m.ZStart_Date = @data and ZSGiftSignIn_d.ZGift_ID in ( case when @Gift='全部' then @allZGift_ID else @ZGift_ID end)
select @sumqtysvip=sum(zqty) from ZSGiftSignIn_d,ZSGiftSignIn_m where ZSGiftSignIn_d.ZGift_ID in (select ZGift_ID from ZSGiftInfo where zGift_Category='白金卡')and ZSGiftSignIn_m.ZVouch_ID=ZSGiftSignIn_d.ZVouch_ID and ZSGiftSignIn_d.ZScore_ID in (case when @ZVIP_Class='全部' then @allZScore_ID else @ZScore_ID end )and ZSGiftSignIn_m.ZStart_Date = @data and ZSGiftSignIn_d.ZGift_ID in (case when @Gift='全部' then @allZGift_ID else @ZGift_ID end)
select @sumqtyallvip=sum(zqty) from ZSGiftSignIn_d,ZSGiftSignIn_m where ZSGiftSignIn_d.ZGift_ID in (select ZGift_ID from ZSGiftInfo where zGift_Category not in ('普通卡','白金卡'))and ZSGiftSignIn_m.ZVouch_ID=ZSGiftSignIn_d.ZVouch_ID and ZSGiftSignIn_d.ZScore_ID in (case when @ZVIP_Class='全部' then @allZScore_ID else @ZScore_ID end) and ZSGiftSignIn_m.ZStart_Date = @data and ZSGiftSignIn_d.ZGift_ID in (case when @Gift='全部' then @allZGift_ID else @ZGift_ID end) select @sumqtyvip,@sumqtysvip,@sumqtyallvip
GO
解决方案 »
- insert into HBTrigger_EnglishNews select id, 'Update', @updateFields from Inser
- sql 2000存储过程中表名改作变量
- 在自动增长列上建立主键可以吗?影不影响效率?
- 如何将SQL 2000或2005的数据库文件导入到SQL2008中?
- 怎么获得自增长的数值
- 求助一个SQL排序问题!
- 问一个sql语句的写法
- 关于NULL值,到底有何良方???
- DELPHI ADO连接MSSQLSERVER,照片字段image,用DBIMAGE关联照片字段,active时显示"bitmap image is no valid"
- 马上给分
- 如何验证本月期初=上月期初+上月入库—上月出库
- SQL病毒
set @var = (select ... from ...)
改成
select @var = ... from ...
set @allZGift_ID=(select ZGift_ID from ZSGiftInfo )这两个语句有问题。
后面查询的结果不止一个。
所以把值赋给@allZScore_ID、@allZGift_ID时有问题。把后面的SQL语句加上条件。或者改用游标。
@allZScore_ID nvarchar(255),
@ZGift_ID nvarchar(255),
@allZGift_ID nvarchar(255),
@sumqtyvip nvarchar(255),
@sumqtysvip nvarchar(255),
@sumqtyallvip nvarchar(255),
@ZVIP_Class nvarchar(255),
@Gift nvarchar(255),
@data nvarchar(255)
set @ZVIP_Class='全部'
set @Gift='全部'
set @data='2004-01-09'select @sumqtyvip=sum(zqty) from ZSGiftSignIn_d,ZSGiftSignIn_m where
ZSGiftSignIn_d.ZGift_ID in (select ZGift_ID from ZSGiftInfo where zGift_Category='普通卡') and ZSGiftSignIn_m.ZVouch_ID=ZSGiftSignIn_d.ZVouch_ID and
exists(select 1 from ZSMemberInfo where ZScore_ID=ZSGiftSignIn_d.ZScore_ID and ZVIP_Class=case when ZVIP_Class='全部' then ZVIP_Class else @ZVIP_Class end)
and ZSGiftSignIn_m.ZStart_Date = @data and
exists(select 1 from ZSMemberInfo where ZGift_ID=ZSGiftSignIn_d.ZGift_ID and ZGift_Desc=case when ZVIP_Class='全部' then ZGift_Desc else @Gift end)select @sumqtysvip=sum(zqty) from ZSGiftSignIn_d,ZSGiftSignIn_m where ZSGiftSignIn_d.ZGift_ID in (select ZGift_ID from ZSGiftInfo where zGift_Category='白金卡')and ZSGiftSignIn_m.ZVouch_ID=ZSGiftSignIn_d.ZVouch_ID and
exists(select 1 from ZSMemberInfo where ZScore_ID=ZSGiftSignIn_d.ZScore_ID and ZVIP_Class=case when ZVIP_Class='全部' then ZVIP_Class else @ZVIP_Class end)
and ZSGiftSignIn_m.ZStart_Date = @data and
exists(select 1 from ZSMemberInfo where ZGift_ID=ZSGiftSignIn_d.ZGift_ID and ZGift_Desc=case when ZVIP_Class='全部' then ZGift_Desc else @Gift end)select @sumqtyallvip=sum(zqty) from ZSGiftSignIn_d,ZSGiftSignIn_m where ZSGiftSignIn_d.ZGift_ID in (select ZGift_ID from ZSGiftInfo where zGift_Category not in ('普通卡','白金卡'))and ZSGiftSignIn_m.ZVouch_ID=ZSGiftSignIn_d.ZVouch_ID and
exists(select 1 from ZSMemberInfo where ZScore_ID=ZSGiftSignIn_d.ZScore_ID and ZVIP_Class=case when ZVIP_Class='全部' then ZVIP_Class else @ZVIP_Class end)
and ZSGiftSignIn_m.ZStart_Date = @data and
exists(select 1 from ZSMemberInfo where ZGift_ID=ZSGiftSignIn_d.ZGift_ID and ZGift_Desc=case when ZVIP_Class='全部' then ZGift_Desc else @Gift end) select @sumqtyvip,@sumqtysvip,@sumqtyallvip
GO
@allZScore_ID nvarchar(255),
@ZGift_ID nvarchar(255),
@allZGift_ID nvarchar(255),
@sumqtyvip nvarchar(255),
@sumqtysvip nvarchar(255),
@sumqtyallvip nvarchar(255),
@ZVIP_Class nvarchar(255),
@Gift nvarchar(255),
@data nvarchar(255)
set @ZVIP_Class='全部'
set @Gift='全部'
set @data='2004-01-09'select @sumqtyvip=sum(zqty) from ZSGiftSignIn_d,ZSGiftSignIn_m where
ZSGiftSignIn_d.ZGift_ID in (select ZGift_ID from ZSGiftInfo where zGift_Category='普通卡') and ZSGiftSignIn_m.ZVouch_ID=ZSGiftSignIn_d.ZVouch_ID and
exists(select 1 from ZSMemberInfo where ZScore_ID=ZSGiftSignIn_d.ZScore_ID and ZVIP_Class=case when @ZVIP_Class='全部' then ZVIP_Class else @ZVIP_Class end)
and ZSGiftSignIn_m.ZStart_Date = @data and
exists(select 1 from ZSMemberInfo where ZGift_ID=ZSGiftSignIn_d.ZGift_ID and ZGift_Desc=case when @Gift='全部' then ZGift_Desc else @Gift end)select @sumqtysvip=sum(zqty) from ZSGiftSignIn_d,ZSGiftSignIn_m where ZSGiftSignIn_d.ZGift_ID in (select ZGift_ID from ZSGiftInfo where zGift_Category='白金卡')and ZSGiftSignIn_m.ZVouch_ID=ZSGiftSignIn_d.ZVouch_ID and
exists(select 1 from ZSMemberInfo where ZScore_ID=ZSGiftSignIn_d.ZScore_ID and ZVIP_Class=case when @ZVIP_Class='全部' then ZVIP_Class else @ZVIP_Class end)
and ZSGiftSignIn_m.ZStart_Date = @data and
exists(select 1 from ZSMemberInfo where ZGift_ID=ZSGiftSignIn_d.ZGift_ID and ZGift_Desc=case when @Gift='全部' then ZGift_Desc else @Gift end)select @sumqtyallvip=sum(zqty) from ZSGiftSignIn_d,ZSGiftSignIn_m where ZSGiftSignIn_d.ZGift_ID in (select ZGift_ID from ZSGiftInfo where zGift_Category not in ('普通卡','白金卡'))and ZSGiftSignIn_m.ZVouch_ID=ZSGiftSignIn_d.ZVouch_ID and
exists(select 1 from ZSMemberInfo where ZScore_ID=ZSGiftSignIn_d.ZScore_ID and ZVIP_Class=case when @ZVIP_Class='全部' then ZVIP_Class else @ZVIP_Class end)
and ZSGiftSignIn_m.ZStart_Date = @data and
exists(select 1 from ZSMemberInfo where ZGift_ID=ZSGiftSignIn_d.ZGift_ID and ZGift_Desc=case when @Gift='全部' then ZGift_Desc else @Gift end) select @sumqtyvip,@sumqtysvip,@sumqtyallvip
GO