java执行sql server存储过程，数据为空时报错列名无效

kingoneyun 2015-09-21 04:56:46

在我的项目里碰到一个很奇怪的问题,用Java代码执行sql server存储过程的时候，某一条查询语句查询数据为空时，Java代码中报错“XXX列名无效“，但是在数据库中查询时不报错?而且更奇怪的是因为项目分不同的角色，所以数据库有多个，同样的SQL语句在一个库中报错，而另外的库中不报错？

以下我的sql语句片段：



                SELECT DISTINCT * INTO #table_cycle FROM(

		SELECT cp.popdb_id, cp.pop_id, cp.pop, cp.address,  

	  		c.city,c.city_e,d.district,d.district_e ,  

	  		ch.channel,isnull(ch.channel_e,'') as channel_e,cha.chain,isnull(cha.chain_e,'') as chain_e,  

	  		isnull(cp.contact_person, '') as contact_person,  

	  		isnull(cp.contact_telephone, '') as contact_telephone,   

	  		isnull(cp.contact_mobile, '') as contact_mobile,  cp.status,CAST (cp.pop AS nvarchar(256)) AS abbr

	  		, pdc.Longitude,pdc.Latitude,2015 AS client_year 

	  		,9 AS client_period ,c.City_ID,d.District_ID,ch.Channel_ID,cha.Chain_ID,38 AS calendar_week,cp.client_id  

	  		,pf.Prefecture,pf.Prefecture_e,pf.Prefecture_id,pv.Province,pv.Province_e,pv.Province_id,c.country_id

	  		,d.City_id AS district_city_id,d.Country_id AS district_country_id

	  		,ch.Channel_order,cp.photo,null as user_id,cp.Comments as Comments 

	  		,pc.pop_cluster,pc.pop_cluster_e

	  FROM dbo.Client_POPDB cp

	  INNER JOIN (SELECT [value] AS POPDB_ID FROM [dbo].[f_split] ('3286',',')) t

	  ON t.POPDB_ID=cp.POPDB_id

	  

	  inner join city c	 on c.city_id=cp.city_id	 

	  left join district d on d.district_id=cp.district_id	 

	  left join channel ch on ch.channel_id=cp.channel_id

	  left join chain cha	 on cha.chain_id=cp.chain_id

	  LEFT JOIN dbo.Prefecture pf ON c.Prefecture_id=pf.Prefecture_id

	  LEFT JOIN dbo.Province pv ON pv.Province_id=pf.Province_id

	  inner join popdb_coordinate pdc on cp.popdb_id = pdc.popdb_id	 

	 		and pdc.Longitude is not null and pdc.Latitude is not null	

	  left join pop_cluster pc on cp.client_id=pc.client_id and cp.pop_cluster_id=pc.pop_cluster_id 

	  WHERE cp.Client_id=1 AND cp.Relevant=1

	  ) temp

          

         select cpc.client_id,cpc.popdb_id,cpc.contact_id,cpc.user_id,cpc.create_date,cpc.update_date 

	   from client_popdb_contact cpc 

           where cpc.client_id=1 and cpc.popdb_id in (select popdb_id from #table_cycle)

字段在表里面都有，两个数据库，一个库可以用，另外的一个就不行！也不知道为什么

Java执行代码：



        StringBuffer sql = new StringBuffer();

	

	sql.append(" DECLARE @d DATETIME ");

	sql.append(date == null ? " set @d = GETDATE() ":" set @d = '"+date+"' " );

        sql.append(" exec Mobile_get_cycle_pop_2_daily_pop_rs"+test+" "+user_id+", @d,"+client_id+","+popdb_ids).append("\n");

       cn   = com.tpm.util.DBUtils.cn();

       stmt = cn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);

       rs   = stmt.executeQuery(sql.toString());

       while (rs.next()) {

			String contact_client_id  = rs.getString("client_id");

			String popdb_id    = rs.getString("popdb_id");

			String contact_id  = rs.getString("contact_id");

			String contact_user_id  = rs.getString("user_id");

			String create_date  = rs.getString("create_date");

			String update_date  = rs.getString("update_date");





		}



		rs.close();