jdbc 调用 sql server 的存储过程时“该语句没有返回结果集”的解决方法

在JDBC中调用SQL Server中的存储过程时出现如下异常： 

1. com.microsoft.sqlserver.jdbc.SQLServerException: 该语句没有返回结果集。   

2.         at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:171)   

3.         at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:394)   

4.         at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:340)   

5.         at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:4575)   

6.         at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1400)   

7.         at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:179)   

8.         at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:154)   

9.         at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(SQLServerPreparedStatement.java:283)  

10. 解决方法，添加下面代码

SET NOCOUNT ON

下面是一个存储过程案例， 里面用到了" 临时表 "技术

1. USE [xngzf]
   GO
   /****** Object:  StoredProcedure [dbo].[PROC_GzfRentMonthZj]    Script Date: 01/03/2014 16:49:50 ******/
   SET ANSI_NULLS ON
   GO
   SET QUOTED_IDENTIFIER ON
   GO
   ALTER proc [dbo].[PROC_GzfRentMonthZj]
   @fyXqCode varchar(50),
   @fyDhCode varchar(50),
   @affairsMonth varchar(50),
   @lastPerson varchar(50), 
   @lastGov  varchar(50)
   as
   	SET NOCOUNT ON
   	declare @htID varchar(50)
   	declare @chengzfID varchar(50)
   	declare @fyID varchar(50)
   	declare @accountID varchar(50)
   	declare @accountTotal decimal(28, 2)
   	declare @fyzjMonth decimal(28, 2)
   	
   	declare @htCode varchar(50)
   	declare @chengzfName varchar(50)
   	declare @fyName varchar(500)
   	declare @acountTotal decimal(28, 2)
   	declare @resultInfo varchar(500)
   	
   	declare @Num int = 0
   	declare @i int = 1
   
   	/**创建临时表，返回错误信息**/
   	create  table  Du_ErrorInfo ( 
   	  htCode varchar(50), 
   	  chengzfName  varchar(50),
   	  fyName  varchar(500),
   	  accountID  varchar(50),
   	  accountTotal  decimal(28, 2),
   	  fyzjMonth decimal(28, 2),
   	  resultInfo  varchar(50) DEFAULT '账户余额不足',
   	)
   	
   	/**获得总数**/
   	set @Num = (select COUNT(*) from tb_xngzf_basic_ht ht
   	left join tb_xngzf_basic_fang fy on ht.fyID = fy.fyID
   	left join tb_xngzf_basic_chengzf czf on ht.chengzfID = czf.zuhuID
   	left join tb_qx_weidu xq on xq.weiduID = fy.fyXqCode	--关联得到小区名称
   	left join tb_qx_weidu dh on dh.weiduID = fy.fyDhCode	--关联得到栋号名称
   	left join tb_xngzf_basic_ht_child chd on ht.htID = chd.htID
   	where 1=1
   	and ht.isPayMonenty = 2
   	and @affairsMonth between substring(replace(chd.startDate, '-', ''), 0, 7) and substring(replace(chd.endDate, '-', ''), 0, 7)
   	and fy.fyXqCode = @fyXqCode
   	and fy.fyDhCode = @fyDhCode)
   	
   	while( @i<=@Num )
   	begin
   		/**查找htID, chengzfID, fyID**/
   		select @htID=fo.htID, @chengzfID=fo.chengzfID, @fyID=fo.fyID, @htCode=fo.htCode, @chengzfName=fo.chengzfName, @fyName=fo.fyName from(
   			select ht.htID, ht.chengzfID, ht.fyID, ROW_NUMBER() over(order by ht.htID) as rowNumber, ht.htCode, czf.zuhuName as chengzfName, xq.weiduName+dh.weiduName+fy.fyFh as fyName from tb_xngzf_basic_ht ht
   			left join tb_xngzf_basic_fang fy on ht.fyID = fy.fyID
   			left join tb_xngzf_basic_chengzf czf on ht.chengzfID = czf.zuhuID
   			left join tb_qx_weidu xq on xq.weiduID = fy.fyXqCode	--关联得到小区名称
   			left join tb_qx_weidu dh on dh.weiduID = fy.fyDhCode	--关联得到栋号名称
   			left join tb_xngzf_basic_ht_child chd on ht.htID = chd.htID
   			where 1=1
   			and ht.isPayMonenty = 2
   			and @affairsMonth between substring(replace(chd.startDate, '-', ''), 0, 7) and substring(replace(chd.endDate, '-', ''), 0, 7)
   			and fy.fyXqCode = @fyXqCode
   			and fy.fyDhCode = @fyDhCode
   		)fo
   		where fo.rowNumber = @i		
   		begin
   		/**判断扣租表里该月份是否已扣租**/
   		if( not exists(select * from tb_xngzf_basic_money_deduct where htID=@htID and affairsMonth=replace(@affairsMonth, '-', '')) )
   			begin 
   				/**查找accountID**/
   				if(@chengzfID is not null)
   				begin
   					select @accountID=accountID from tb_xngzf_basic_money_account where chengzfID = @chengzfID
   				end
   				set @accountTotal = (select accountTotal from tb_xngzf_basic_money_account where accountID = @accountID)
   				set @fyzjMonth = (select fyZujinMonth from tb_xngzf_basic_ht_child where htID = @htID)
   				--set @accountTotal = 50
   				--set @fyzjMonth = 100
   				if(@accountTotal>=@fyzjMonth)
   				begin
   					begin tran
   					--print @htID + ',' + @chengzfID + ',' + @fyID
   					/**更新账户表的账户金额**/
   					update tb_xngzf_basic_money_account set expensesTotal=expensesTotal+@fyzjMonth, accountTotal=accountTotal-@fyzjMonth where accountID=@accountID
   					if @@ERROR <> 0
   					begin 
   						rollback tran
   					end
   					/**插入账户子表一条扣费记录**/
   					insert into tb_xngzf_basic_money_account_child(feeID, accountID, chengzfID, szType, income, expenses, accountTotal, scDesc, lastTime, lastPerson, lastGov)
   					values((select MAX(CONVERT(bigint, feeID)+1) from tb_xngzf_basic_money_account_child), @accountID, @chengzfID, 4, 0, @fyzjMonth, @accountTotal-@fyzjMonth, '日常扣租', (select convert(nvarchar,getdate(),23)), @lastPerson, @lastGov)
   					if @@ERROR <> 0
   					begin 
   						rollback tran
   					end
   					/**插入扣租表一条扣租记录**/
   					insert into tb_xngzf_basic_money_deduct(kfID, htID, fyID, chengzfID, affairsMonth, sfMoney, sfDesc, lastTime, lastPerson, lastGov)
   					values((select MAX(CONVERT(bigint, kfID)+1) from tb_xngzf_basic_money_deduct), @htID, @fyID, @chengzfID, replace(@affairsMonth, '-', ''), @fyzjMonth, '来自扣租', (select convert(nvarchar,getdate(),23)), @lastPerson, @lastGov)
   					if @@ERROR <> 0
   					begin 
   						rollback tran
   					end
   					commit tran
   				end
   				else
   				begin	
   					--插入临时表一条记录
   					insert into Du_ErrorInfo(htCode, chengzfName, fyName, accountID, accountTotal, fyzjMonth, resultInfo)
   					values(@htCode, @chengzfName, @fyName, @accountID, @accountTotal, @fyzjMonth, '账户余额不足')
   				end
   			end
   			set @i=@i+1
   		end
   	end
   	--查询临时表记录
   	select due.htCode, due.chengzfName, due.fyName, due.accountID, due.accountTotal, due.fyzjMonth, due.resultInfo from Du_ErrorInfo due
   	--删除临时表
   	drop table Du_ErrorInfo
   	
   	--调存储过程
   	exec PROC_GzfRentMonthZj  '77', '78', '2014-06', '1', ''