DECLARE T_cursor CURSOR FOR SELECT ID,Name FROM sysobjects declare @ID int,@name sysname OPEN T_cursor FETCH NEXT FROM T_cursor into @ID,@Name while @@fetch_status=0 begin select @ID,@Name--調用FETCH NEXT FROM T_cursor into @ID,@Nameend close T_cursor deallocate T_cursor
我想在一个存储过程中执行这几个sql语句,把每一个语句的结果保持在一个相应的参数里然后返回 --统计各单位住院费用: select Sum(ReimburseSum) as TotalIhpRbSum from HRW_YG_MedicalRecord join v_HRW_YG_BscEmployeeInfo on v_HRW_YG_BscEmployeeInfo.EM_ID=HRW_YG_MedicalRecord.EM_ID where v_HRW_YG_BscEmployeeInfo.UnitID=@unitID AND ReimburseType='15' and v_HRW_YG_BscEmployeeInfo.Status='1';--统计各单位住院人次: select count(*) as TotalIhpPeople from HRW_YG_MedicalRecord join v_HRW_YG_BscEmployeeInfo on v_HRW_YG_BscEmployeeInfo.EM_ID=HRW_YG_MedicalRecord.EM_ID where v_HRW_YG_BscEmployeeInfo.UnitID='{7AE0A3FB-2D6C-4E51-8B10-71EAFA2D269B}'AND ReimburseType='15';--统计各单位特劳保人数: select count(TLBNo) as TotalTlbPeople from HRW_YG_TLBInfo join HM_EM_Family_Society on HRW_YG_TLBInfo.RelationID=HM_EM_Family_Society.ID join v_HRW_YG_BscEmployeeInfo on v_HRW_YG_BscEmployeeInfo.EM_ID=HM_EM_Family_Society.EM_ID where UnitID=@unitID;--各单位特劳保门诊住院报销总额:select SUM(MZReimburseSum) as TlbTotalClcRebSum,SUM(ZYReimburseSum) as TlbTotalIhpRbSum,SUM(MZReimburseSum+ZYReimburseSum) as TotalSum from HRW_YG_TLBInfo join HM_EM_Family_Society on HRW_YG_TLBInfo.RelationID=HM_EM_Family_Society.ID join v_HRW_YG_BscEmployeeInfo on v_HRW_YG_BscEmployeeInfo.EM_ID=HM_EM_Family_Society.EM_ID join HRW_YG_TLBMedicalRecord on HRW_YG_TLBMedicalRecord.RelationID=HM_EM_Family_Society.ID where UnitID=@unitID; 我视图把这几个sql语句合并起来但总是搞不定 所以只能分别执行了,又不想在程序中写这么多,所以想用存储过程来实现。
FOR SELECT ID,Name FROM sysobjects
declare @ID int,@name sysname
OPEN T_cursor
FETCH NEXT FROM T_cursor into @ID,@Name
while @@fetch_status=0
begin
select @ID,@Name--調用FETCH NEXT FROM T_cursor into @ID,@Nameend
close T_cursor
deallocate T_cursor
動態游標
http://blog.csdn.net/roy_88/archive/2007/11/09/1875264.aspx
--统计各单位住院费用:
select Sum(ReimburseSum) as TotalIhpRbSum from HRW_YG_MedicalRecord join v_HRW_YG_BscEmployeeInfo on v_HRW_YG_BscEmployeeInfo.EM_ID=HRW_YG_MedicalRecord.EM_ID where v_HRW_YG_BscEmployeeInfo.UnitID=@unitID AND ReimburseType='15' and v_HRW_YG_BscEmployeeInfo.Status='1';--统计各单位住院人次:
select count(*) as TotalIhpPeople from HRW_YG_MedicalRecord join v_HRW_YG_BscEmployeeInfo on v_HRW_YG_BscEmployeeInfo.EM_ID=HRW_YG_MedicalRecord.EM_ID where v_HRW_YG_BscEmployeeInfo.UnitID='{7AE0A3FB-2D6C-4E51-8B10-71EAFA2D269B}'AND ReimburseType='15';--统计各单位特劳保人数:
select count(TLBNo) as TotalTlbPeople from HRW_YG_TLBInfo join HM_EM_Family_Society on HRW_YG_TLBInfo.RelationID=HM_EM_Family_Society.ID join
v_HRW_YG_BscEmployeeInfo on v_HRW_YG_BscEmployeeInfo.EM_ID=HM_EM_Family_Society.EM_ID where UnitID=@unitID;--各单位特劳保门诊住院报销总额:select SUM(MZReimburseSum) as TlbTotalClcRebSum,SUM(ZYReimburseSum) as TlbTotalIhpRbSum,SUM(MZReimburseSum+ZYReimburseSum) as TotalSum from HRW_YG_TLBInfo join HM_EM_Family_Society on HRW_YG_TLBInfo.RelationID=HM_EM_Family_Society.ID join
v_HRW_YG_BscEmployeeInfo on v_HRW_YG_BscEmployeeInfo.EM_ID=HM_EM_Family_Society.EM_ID join
HRW_YG_TLBMedicalRecord on HRW_YG_TLBMedicalRecord.RelationID=HM_EM_Family_Society.ID where UnitID=@unitID;
我视图把这几个sql语句合并起来但总是搞不定
所以只能分别执行了,又不想在程序中写这么多,所以想用存储过程来实现。
把每一个sql语句做成存储过程倒是可以,但这样我觉得太野蛮了,想把他们放在一个存储过程里面,可这样我有不知道如何在一个存储过程里执行多条sql语句并返回每条sql语句的结果