sqlserver调用存储过程返回结果集

《sqlserver调用存储过程返回结果集》由会员上传分享，免费在线阅读，更多相关内容在工程资料-天天文库。

1、USE[zhuhaioa7-4]GO/******Object:StoredProcedure[dbo].[proc_records]ScriptDate:12/26/201420:31:09******/SETANSI_NULLSONGOSETQUOTED_IDENTIFIERONGOALTERprocedure[dbo].[proc_records]asDECLARE@map_tabletable(r_keyvarchar(100),r_valuevarchar(50))DECLARE@type_idvarchar(40)DECLARE@type_namevarcha

2、r(50)DECLARE@project_idvarchar(40)DECLARE@project_namevarchar(50)DECLARE@payTypeMoneynumeric(16,2)DECLARE@sumPayTypeMoneynumeric(16,2)BEGINset@payTypeMoney=0set@sumPayTypeMoney=0--查询项目列表DECLAREproject_cursorCURSORforselectep_id,ep_namefromAB_engineeringPhasewhereaccount_id='2'openproject_

3、cursorfetchnextfromproject_cursorinto@project_id,@project_namewhile@@FETCH_STATUS=0begin--查询项目支付类别DECLAREprojectType_cursorCURSORforselectid,namefromT_DataItemwheretypeId='payType'orderbysortascopenprojectType_cursorfetchnextfromprojectType_cursorinto@type_id,@type_namewhile@@FETCH_STATUS

4、=0begin--业务逻辑处理处理内层游标--获取一个项目某一项支付类型的数据--查询一个项目某一支付类别金额select@payTypeMoney=sum(checkProjectPayMoney)fromJiLiangZhiFuwhereprojectId=@project_idandpayType=@type_idgroupbypayType--一个项目某一支付类别金额存入insertinto@map_tablevalues(@project_id+','+@type_id,@payTypeMoney)--一个项目累计支付类别金额set@sumPayTypeMone

5、y=@sumPayTypeMoney+@payTypeMoney--一个项目某一支付类别金额重置为set@payTypeMoney=0fetchnextfromprojectType_cursorinto@type_id,@type_name--内层游标向下移动一行end--插入某一项目各个支付类别的金额合计insertinto@map_tablevalues(@project_id,@sumPayTypeMoney)set@sumPayTypeMoney=0closeprojectType_cursordeallocateprojectType_cursorfetchn

6、extfromproject_cursorinto@project_id,@project_name--内层游标处理结束后,外层游标才继续向下移动一行endcloseproject_cursordeallocateproject_cursorselect*from@map_tableENDpublicMapgetProjectPayMoneyByPayType(){try{return(Map)this.getHibernateTemplate().execute(newHibernateCallback(){publicObjectdoInHibernate(Sessi

7、onsession)throwsHibernateException,SQLException{Connectioncon=session.connection();Statementstmt=con.createStatement();CallableStatementcs=con.prepareCall("{callproc_records}");//ResultSetrs=stmt.executeQuery("{callproc_records}");ResultSetrs=cs.executeQuery();Mapma