下面两个ExecuteReader(),sqlDataReader1 语句可以运行,sqlDataReader2报错误
消息 6522,级别 16,状态 1,第 1 行
在执行用户定义例程或聚合 "UF_Table_GetReturnData" 期间出现 .NET Framework 错误:
System.Data.SqlClient.SqlException: 函数中含有的 SELECT 语句无法向客户端返回数据。
--------------------------------------------------------------------
-------------------------------------------------------------------- SqlDataReader sqlDataReader1 = new SqlCommand
{
Connection = sqlConnection,
CommandText = " select 'GJ' as BOOKNAME ,* FROM (select (select top 1 FValue from DB201207GJ..t_systemprofile where fkey='companyname') as 公司名, a.会计年度,a.会计期间,a.币别,isnull(a.期初余额,0)as '期初余额',isnull(b.借方合计,0) as '借方合计',isnull(b.贷方合计,0) as '贷方合计',(isnull(a.期初余额,0)+isnull(b.借方合计,0)-isnull(b.贷方合计,0))as '期末余额' from ( select CAST(FYear AS varchar) as 会计年度,right('00'+cast(FPeriod as varchar(2)),2) as 会计期间,t3.fname as 币别,isnull(SUM(FBeginBalance),0) as 期初余额,isnull(SUM(FDebit),0) as 期间借方合计, isnull(Sum(Fcredit),0) as 期间贷方合计,isnull(SUM(fendbalance),0) as 期末余额 from DB201207GJ..t_Balance t1 left join DB201207GJ..t_Account t2 on t1.FAccountID=t2.FAccountID left join DB201207GJ..t_currency t3 on t1.fcurrencyid=t3.fcurrencyid where t2.FIsBank|t2.FIsCash=1 and t2.FLevel=1 and t1.FCurrencyID<>0 group by CAST(FYear AS varchar),right('00'+cast(FPeriod as varchar(2)),2),t3.fname ) a left join (select 会计年度,会计期间,币别,isnull(sum(借),0) as '借方合计',isnull(sum(贷),0) as '贷方合计' from (select case a.fdc when 1 then a.FAmount else 0 end 借, case a.fdc when 0 then a.FAmount else 0 end 贷, f.fname as 币别, cast(fyear as varchar) AS '会计年度',right('00'+cast(FPeriod as varchar(2)),2) as '会计期间' from DB201207GJ..t_VoucherEntry a left join DB201207GJ..t_Account b on a.FAccountID =b.FAccountID left join DB201207GJ..t_voucher c on a.FVoucherID=c.FVoucherID left join DB201207GJ..t_VoucherGroup d on c.FGroupID=d.FGroupID left join DB201207GJ..t_currency f on f.fcurrencyid=a.fcurrencyid where b.FIsBank|b.FIsCash=1 and f.FCurrencyID<>0 ) afdsf group by 会计年度,会计期间,币别) b on a.会计年度=b.会计年度 and a.会计期间=b.会计期间 and a.币别=b.币别) as TABLE0 select 'GJ' as BOOKNAME ,* FROM (select (select top 1 FValue from DB201207GJ..t_systemprofile where fkey='companyname') as 公司名, a.会计年度,a.会计期间,a.币别,isnull(a.期初余额,0)as '期初余额',isnull(b.借方合计,0) as '借方合计',isnull(b.贷方合计,0) as '贷方合计',(isnull(a.期初余额,0)+isnull(b.借方合计,0)-isnull(b.贷方合计,0))as '期末余额' from ( select CAST(FYear AS varchar) as 会计年度,right('00'+cast(FPeriod as varchar(2)),2) as 会计期间,t3.fname as 币别,isnull(SUM(FBeginBalance),0) as 期初余额,isnull(SUM(FDebit),0) as 期间借方合计, isnull(Sum(Fcredit),0) as 期间贷方合计,isnull(SUM(fendbalance),0) as 期末余额 from DB201207GJ..t_Balance t1 left join DB201207GJ..t_Account t2 on t1.FAccountID=t2.FAccountID left join DB201207GJ..t_currency t3 on t1.fcurrencyid=t3.fcurrencyid where t2.FIsBank|t2.FIsCash=1 and t2.FLevel=1 and t1.FCurrencyID<>0 group by CAST(FYear AS varchar),right('00'+cast(FPeriod as varchar(2)),2),t3.fname ) a left join (select 会计年度,会计期间,币别,isnull(sum(借),0) as '借方合计',isnull(sum(贷),0) as '贷方合计' from (select case a.fdc when 1 then a.FAmount else 0 end 借, case a.fdc when 0 then a.FAmount else 0 end 贷, f.fname as 币别, cast(fyear as varchar) AS '会计年度',right('00'+cast(FPeriod as varchar(2)),2) as '会计期间' from DB201207GJ..t_VoucherEntry a left join DB201207GJ..t_Account b on a.FAccountID =b.FAccountID left join DB201207GJ..t_voucher c on a.FVoucherID=c.FVoucherID left join DB201207GJ..t_VoucherGroup d on c.FGroupID=d.FGroupID left join DB201207GJ..t_currency f on f.fcurrencyid=a.fcurrencyid where b.FIsBank|b.FIsCash=1 and f.FCurrencyID<>0 ) afdsf group by 会计年度,会计期间,币别) b on a.会计年度=b.会计年度 and a.会计期间=b.会计期间 and a.币别=b.币别) as TABLE0 union select 'BZ' as BOOKNAME ,* FROM (select (select top 1 FValue from DB20120712BZ..t_systemprofile where fkey='companyname') as 公司名, a.会计年度,a.会计期间,a.币别,isnull(a.期初余额,0)as '期初余额',isnull(b.借方合计,0) as '借方合计',isnull(b.贷方合计,0) as '贷方合计',(isnull(a.期初余额,0)+isnull(b.借方合计,0)-isnull(b.贷方合计,0))as '期末余额' from ( select CAST(FYear AS varchar) as 会计年度,right('00'+cast(FPeriod as varchar(2)),2) as 会计期间,t3.fname as 币别,isnull(SUM(FBeginBalance),0) as 期初余额,isnull(SUM(FDebit),0) as 期间借方合计, isnull(Sum(Fcredit),0) as 期间贷方合计,isnull(SUM(fendbalance),0) as 期末余额 from DB20120712BZ..t_Balance t1 left join DB20120712BZ..t_Account t2 on t1.FAccountID=t2.FAccountID left join DB20120712BZ..t_currency t3 on t1.fcurrencyid=t3.fcurrencyid where t2.FIsBank|t2.FIsCash=1 and t2.FLevel=1 and t1.FCurrencyID<>0 group by CAST(FYear AS varchar),right('00'+cast(FPeriod as varchar(2)),2),t3.fname ) a left join (select 会计年度,会计期间,币别,isnull(sum(借),0) as '借方合计',isnull(sum(贷),0) as '贷方合计' from (select case a.fdc when 1 then a.FAmount else 0 end 借, case a.fdc when 0 then a.FAmount else 0 end 贷, f.fname as 币别, cast(fyear as varchar) AS '会计年度',right('00'+cast(FPeriod as varchar(2)),2) as '会计期间' from DB20120712BZ..t_VoucherEntry a left join DB20120712BZ..t_Account b on a.FAccountID =b.FAccountID left join DB20120712BZ..t_voucher c on a.FVoucherID=c.FVoucherID left join DB20120712BZ..t_VoucherGroup d on c.FGroupID=d.FGroupID left join DB20120712BZ..t_currency f on f.fcurrencyid=a.fcurrencyid where b.FIsBank|b.FIsCash=1 and f.FCurrencyID<>0 ) afdsf group by 会计年度,会计期间,币别) b on a.会计年度=b.会计年度 and a.会计期间=b.会计期间 and a.币别=b.币别) as TABLE1"}.ExecuteReader();
sqlDataReader1.Dispose();
--------------------------------------------------------------------
消息 6522,级别 16,状态 1,第 1 行
在执行用户定义例程或聚合 "UF_Table_GetReturnData" 期间出现 .NET Framework 错误:
System.Data.SqlClient.SqlException: 函数中含有的 SELECT 语句无法向客户端返回数据。
--------------------------------------------------------------------
-------------------------------------------------------------------- SqlDataReader sqlDataReader1 = new SqlCommand
{
Connection = sqlConnection,
CommandText = " select 'GJ' as BOOKNAME ,* FROM (select (select top 1 FValue from DB201207GJ..t_systemprofile where fkey='companyname') as 公司名, a.会计年度,a.会计期间,a.币别,isnull(a.期初余额,0)as '期初余额',isnull(b.借方合计,0) as '借方合计',isnull(b.贷方合计,0) as '贷方合计',(isnull(a.期初余额,0)+isnull(b.借方合计,0)-isnull(b.贷方合计,0))as '期末余额' from ( select CAST(FYear AS varchar) as 会计年度,right('00'+cast(FPeriod as varchar(2)),2) as 会计期间,t3.fname as 币别,isnull(SUM(FBeginBalance),0) as 期初余额,isnull(SUM(FDebit),0) as 期间借方合计, isnull(Sum(Fcredit),0) as 期间贷方合计,isnull(SUM(fendbalance),0) as 期末余额 from DB201207GJ..t_Balance t1 left join DB201207GJ..t_Account t2 on t1.FAccountID=t2.FAccountID left join DB201207GJ..t_currency t3 on t1.fcurrencyid=t3.fcurrencyid where t2.FIsBank|t2.FIsCash=1 and t2.FLevel=1 and t1.FCurrencyID<>0 group by CAST(FYear AS varchar),right('00'+cast(FPeriod as varchar(2)),2),t3.fname ) a left join (select 会计年度,会计期间,币别,isnull(sum(借),0) as '借方合计',isnull(sum(贷),0) as '贷方合计' from (select case a.fdc when 1 then a.FAmount else 0 end 借, case a.fdc when 0 then a.FAmount else 0 end 贷, f.fname as 币别, cast(fyear as varchar) AS '会计年度',right('00'+cast(FPeriod as varchar(2)),2) as '会计期间' from DB201207GJ..t_VoucherEntry a left join DB201207GJ..t_Account b on a.FAccountID =b.FAccountID left join DB201207GJ..t_voucher c on a.FVoucherID=c.FVoucherID left join DB201207GJ..t_VoucherGroup d on c.FGroupID=d.FGroupID left join DB201207GJ..t_currency f on f.fcurrencyid=a.fcurrencyid where b.FIsBank|b.FIsCash=1 and f.FCurrencyID<>0 ) afdsf group by 会计年度,会计期间,币别) b on a.会计年度=b.会计年度 and a.会计期间=b.会计期间 and a.币别=b.币别) as TABLE0 select 'GJ' as BOOKNAME ,* FROM (select (select top 1 FValue from DB201207GJ..t_systemprofile where fkey='companyname') as 公司名, a.会计年度,a.会计期间,a.币别,isnull(a.期初余额,0)as '期初余额',isnull(b.借方合计,0) as '借方合计',isnull(b.贷方合计,0) as '贷方合计',(isnull(a.期初余额,0)+isnull(b.借方合计,0)-isnull(b.贷方合计,0))as '期末余额' from ( select CAST(FYear AS varchar) as 会计年度,right('00'+cast(FPeriod as varchar(2)),2) as 会计期间,t3.fname as 币别,isnull(SUM(FBeginBalance),0) as 期初余额,isnull(SUM(FDebit),0) as 期间借方合计, isnull(Sum(Fcredit),0) as 期间贷方合计,isnull(SUM(fendbalance),0) as 期末余额 from DB201207GJ..t_Balance t1 left join DB201207GJ..t_Account t2 on t1.FAccountID=t2.FAccountID left join DB201207GJ..t_currency t3 on t1.fcurrencyid=t3.fcurrencyid where t2.FIsBank|t2.FIsCash=1 and t2.FLevel=1 and t1.FCurrencyID<>0 group by CAST(FYear AS varchar),right('00'+cast(FPeriod as varchar(2)),2),t3.fname ) a left join (select 会计年度,会计期间,币别,isnull(sum(借),0) as '借方合计',isnull(sum(贷),0) as '贷方合计' from (select case a.fdc when 1 then a.FAmount else 0 end 借, case a.fdc when 0 then a.FAmount else 0 end 贷, f.fname as 币别, cast(fyear as varchar) AS '会计年度',right('00'+cast(FPeriod as varchar(2)),2) as '会计期间' from DB201207GJ..t_VoucherEntry a left join DB201207GJ..t_Account b on a.FAccountID =b.FAccountID left join DB201207GJ..t_voucher c on a.FVoucherID=c.FVoucherID left join DB201207GJ..t_VoucherGroup d on c.FGroupID=d.FGroupID left join DB201207GJ..t_currency f on f.fcurrencyid=a.fcurrencyid where b.FIsBank|b.FIsCash=1 and f.FCurrencyID<>0 ) afdsf group by 会计年度,会计期间,币别) b on a.会计年度=b.会计年度 and a.会计期间=b.会计期间 and a.币别=b.币别) as TABLE0 union select 'BZ' as BOOKNAME ,* FROM (select (select top 1 FValue from DB20120712BZ..t_systemprofile where fkey='companyname') as 公司名, a.会计年度,a.会计期间,a.币别,isnull(a.期初余额,0)as '期初余额',isnull(b.借方合计,0) as '借方合计',isnull(b.贷方合计,0) as '贷方合计',(isnull(a.期初余额,0)+isnull(b.借方合计,0)-isnull(b.贷方合计,0))as '期末余额' from ( select CAST(FYear AS varchar) as 会计年度,right('00'+cast(FPeriod as varchar(2)),2) as 会计期间,t3.fname as 币别,isnull(SUM(FBeginBalance),0) as 期初余额,isnull(SUM(FDebit),0) as 期间借方合计, isnull(Sum(Fcredit),0) as 期间贷方合计,isnull(SUM(fendbalance),0) as 期末余额 from DB20120712BZ..t_Balance t1 left join DB20120712BZ..t_Account t2 on t1.FAccountID=t2.FAccountID left join DB20120712BZ..t_currency t3 on t1.fcurrencyid=t3.fcurrencyid where t2.FIsBank|t2.FIsCash=1 and t2.FLevel=1 and t1.FCurrencyID<>0 group by CAST(FYear AS varchar),right('00'+cast(FPeriod as varchar(2)),2),t3.fname ) a left join (select 会计年度,会计期间,币别,isnull(sum(借),0) as '借方合计',isnull(sum(贷),0) as '贷方合计' from (select case a.fdc when 1 then a.FAmount else 0 end 借, case a.fdc when 0 then a.FAmount else 0 end 贷, f.fname as 币别, cast(fyear as varchar) AS '会计年度',right('00'+cast(FPeriod as varchar(2)),2) as '会计期间' from DB20120712BZ..t_VoucherEntry a left join DB20120712BZ..t_Account b on a.FAccountID =b.FAccountID left join DB20120712BZ..t_voucher c on a.FVoucherID=c.FVoucherID left join DB20120712BZ..t_VoucherGroup d on c.FGroupID=d.FGroupID left join DB20120712BZ..t_currency f on f.fcurrencyid=a.fcurrencyid where b.FIsBank|b.FIsCash=1 and f.FCurrencyID<>0 ) afdsf group by 会计年度,会计期间,币别) b on a.会计年度=b.会计年度 and a.会计期间=b.会计期间 and a.币别=b.币别) as TABLE1"}.ExecuteReader();
sqlDataReader1.Dispose();
--------------------------------------------------------------------
SqlDataReader sqlDataReader2 = new SqlCommand
{
Connection = sqlConnection,
CommandText = " select 'GJ' as BOOKNAME ,* FROM (select (select top 1 FValue from DB201207GJ..t_systemprofile where fkey='companyname') as 公司名,newid() as PK,(case when atype='收入' and (FFullName like '预收账款%' or FFullName like '主营业务收入%'or FFullName like '其他业务 收入%') then '销售收入' when (len(往来单位) >1) and ( FFullName like '其他应付款%' or FFullName like '其他应收款 %') then '集团往来' when atype='收入' and (FFullName like '实收资本%' or FFullName like '短期借款%' or FFullName like '长期借款%') then '外部融资' when atype='支出' and (FFullName like '开发成本%' or FFullName like '预付账款%' or FFullName like '应付账款%') then '项目工程支出' when atype='支出' and (FFullName like '销售费用 %' or FFullName like '管理费用%' or FFullName like '财务费用%') then '期间费用支出' when atype='支出' and (FFullName like '短期借款%' or FFullName like '长期借款%' ) then '归还借款' else case when atype='收入' then ' 其它收入' when atype='支出' then '其它支出' end end) as 细类,atype as 大类, -1*afdc*(debit+credit) as 余额, CurrencyName as 币别, cast(fyear as varchar) AS '会计年度',right('00'+cast(FPeriod as varchar(2)),2) as '会计期 间' from ( select (case when CHARINDEX ('收',d.FName)>0 then '收入' when CHARINDEX ('付',d.FName)>0 then '支 出' end )as atype , case a.FDC when 0 then '贷' when 1 then '借' end as 借贷,b.FFullName, b.fname,b.FNumber,c.fyear,c.FPeriod, (select t2.ffullname from DB201207GJ..t_ItemDetailv t1 left join DB201207GJ..t_item t2 on t1.FItemClassID=t2.FItemClassID and t1.FItemID=t2.FItemID left join DB201207GJ..t_itemclass t3 on t1.FItemClassID=t3.FItemClassID where t3.fname='往来单位' and t2.fnumber like '01%' and a.fdetailid=t1.FDetailID) as 往来单位, case a.fdc when 1 then a.FAmount else 0 end DEBIT, case a.fdc when 0 then a.FAmount else 0 end credit, case a.FDC when 1 then 1 when 0 then -1 end as aFdc, f.fname as CurrencyName, a.* from DB201207GJ..t_VoucherEntry as a left join DB201207GJ..t_Account b on a.FAccountID =b.FAccountID left join DB201207GJ..t_voucher c on a.FVoucherID=c.FVoucherID left join DB201207GJ..t_VoucherGroup d on c.FGroupID=d.FGroupID left join DB201207GJ..t_currency f on f.fcurrencyid=a.fcurrencyid where CHARINDEX ('收',d.FName)>0 and b.fnumber not like '1002%' and b.fnumber not like '1001%' union select (case when CHARINDEX ('收',d.FName)>0 then '收入' when CHARINDEX ('付',d.FName)>0 then '支出' end )as atype , case a.FDC when 0 then '贷' when 1 then '借' end as 借贷,b.FFullName, b.fname,b.FNumber,c.fyear,c.FPeriod, (select t2.ffullname from DB201207GJ..t_ItemDetailv t1 left join DB201207GJ..t_item t2 on t1.FItemClassID=t2.FItemClassID and t1.FItemID=t2.FItemID left join DB201207GJ..t_itemclass t3 on t1.FItemClassID=t3.FItemClassID where t3.fname='往来单位' and t2.fnumber like '01%' and a.fdetailid=t1.FDetailID) as 往来单位, case a.fdc when 1 then a.FAmount else 0 end DEBIT, case a.fdc when 0 then a.FAmount else 0 end credit, case a.FDC when 1 then 1 when 0 then -1 end as aFdc, f.fname as CurrencyName, a.* from DB201207GJ..t_VoucherEntry as a left join DB201207GJ..t_Account b on a.FAccountID =b.FAccountID left join DB201207GJ..t_voucher c on a.FVoucherID=c.FVoucherID left join DB201207GJ..t_VoucherGroup d on c.FGroupID=d.FGroupID left join DB201207GJ..t_currency f on f.fcurrencyid=a.fcurrencyid where CHARINDEX ('付',d.FName)>0 and b.fnumber not like '1002%' and b.fnumber not like '1001%' ) as k33 ) as TABLE0 select 'GJ' as BOOKNAME ,* FROM (select (select top 1 FValue from DB201207GJ..t_systemprofile where fkey='companyname') as 公司名,newid() as PK,(case when atype='收入' and (FFullName like '预收账款%' or FFullName like '主营业务收入%'or FFullName like '其他业务 收入%') then '销售收入' when (len(往来单位) >1) and ( FFullName like '其他应付款%' or FFullName like '其他应收款 %') then '集团往来' when atype='收入' and (FFullName like '实收资本%' or FFullName like '短期借款%' or FFullName like '长期借款%') then '外部融资' when atype='支出' and (FFullName like '开发成本%' or FFullName like '预付账款%' or FFullName like '应付账款%') then '项目工程支出' when atype='支出' and (FFullName like '销售费用 %' or FFullName like '管理费用%' or FFullName like '财务费用%') then '期间费用支出' when atype='支出' and (FFullName like '短期借款%' or FFullName like '长期借款%' ) then '归还借款' else case when atype='收入' then ' 其它收入' when atype='支出' then '其它支出' end end) as 细类,atype as 大类, -1*afdc*(debit+credit) as 余额, CurrencyName as 币别, cast(fyear as varchar) AS '会计年度',right('00'+cast(FPeriod as varchar(2)),2) as '会计期 间' from ( select (case when CHARINDEX ('收',d.FName)>0 then '收入' when CHARINDEX ('付',d.FName)>0 then '支 出' end )as atype , case a.FDC when 0 then '贷' when 1 then '借' end as 借贷,b.FFullName, b.fname,b.FNumber,c.fyear,c.FPeriod, (select t2.ffullname from DB201207GJ..t_ItemDetailv t1 left join DB201207GJ..t_item t2 on t1.FItemClassID=t2.FItemClassID and t1.FItemID=t2.FItemID left join DB201207GJ..t_itemclass t3 on t1.FItemClassID=t3.FItemClassID where t3.fname='往来单位' and t2.fnumber like '01%' and a.fdetailid=t1.FDetailID) as 往来单位, case a.fdc when 1 then a.FAmount else 0 end DEBIT, case a.fdc when 0 then a.FAmount else 0 end credit, case a.FDC when 1 then 1 when 0 then -1 end as aFdc, f.fname as CurrencyName, a.* from DB201207GJ..t_VoucherEntry as a left join DB201207GJ..t_Account b on a.FAccountID =b.FAccountID left join DB201207GJ..t_voucher c on a.FVoucherID=c.FVoucherID left join DB201207GJ..t_VoucherGroup d on c.FGroupID=d.FGroupID left join DB201207GJ..t_currency f on f.fcurrencyid=a.fcurrencyid where CHARINDEX ('收',d.FName)>0 and b.fnumber not like '1002%' and b.fnumber not like '1001%' union select (case when CHARINDEX ('收',d.FName)>0 then '收入' when CHARINDEX ('付',d.FName)>0 then '支出' end )as atype , case a.FDC when 0 then '贷' when 1 then '借' end as 借贷,b.FFullName, b.fname,b.FNumber,c.fyear,c.FPeriod, (select t2.ffullname from DB201207GJ..t_ItemDetailv t1 left join DB201207GJ..t_item t2 on t1.FItemClassID=t2.FItemClassID and t1.FItemID=t2.FItemID left join DB201207GJ..t_itemclass t3 on t1.FItemClassID=t3.FItemClassID where t3.fname='往来单位' and t2.fnumber like '01%' and a.fdetailid=t1.FDetailID) as 往来单位, case a.fdc when 1 then a.FAmount else 0 end DEBIT, case a.fdc when 0 then a.FAmount else 0 end credit, case a.FDC when 1 then 1 when 0 then -1 end as aFdc, f.fname as CurrencyName, a.* from DB201207GJ..t_VoucherEntry as a left join DB201207GJ..t_Account b on a.FAccountID =b.FAccountID left join DB201207GJ..t_voucher c on a.FVoucherID=c.FVoucherID left join DB201207GJ..t_VoucherGroup d on c.FGroupID=d.FGroupID left join DB201207GJ..t_currency f on f.fcurrencyid=a.fcurrencyid where CHARINDEX ('付',d.FName)>0 and b.fnumber not like '1002%' and b.fnumber not like '1001%' ) as k33 ) as TABLE0 union select 'BZ' as BOOKNAME ,* FROM (select (select top 1 FValue from DB20120712BZ..t_systemprofile where fkey='companyname') as 公司名,newid() as PK,(case when atype='收入' and (FFullName like '预收账款%' or FFullName like '主营业务收入%'or FFullName like '其他业务 收入%') then '销售收入' when (len(往来单位) >1) and ( FFullName like '其他应付款%' or FFullName like '其他应收款 %') then '集团往来' when atype='收入' and (FFullName like '实收资本%' or FFullName like '短期借款%' or FFullName like '长期借款%') then '外部融资' when atype='支出' and (FFullName like '开发成本%' or FFullName like '预付账款%' or FFullName like '应付账款%') then '项目工程支出' when atype='支出' and (FFullName like '销售费用 %' or FFullName like '管理费用%' or FFullName like '财务费用%') then '期间费用支出' when atype='支出' and (FFullName like '短期借款%' or FFullName like '长期借款%' ) then '归还借款' else case when atype='收入' then ' 其它收入' when atype='支出' then '其它支出' end end) as 细类,atype as 大类, -1*afdc*(debit+credit) as 余额, CurrencyName as 币别, cast(fyear as varchar) AS '会计年度',right('00'+cast(FPeriod as varchar(2)),2) as '会计期 间' from ( select (case when CHARINDEX ('收',d.FName)>0 then '收入' when CHARINDEX ('付',d.FName)>0 then '支 出' end )as atype , case a.FDC when 0 then '贷' when 1 then '借' end as 借贷,b.FFullName, b.fname,b.FNumber,c.fyear,c.FPeriod, (select t2.ffullname from DB20120712BZ..t_ItemDetailv t1 left join DB20120712BZ..t_item t2 on t1.FItemClassID=t2.FItemClassID and t1.FItemID=t2.FItemID left join DB20120712BZ..t_itemclass t3 on t1.FItemClassID=t3.FItemClassID where t3.fname='往来单位' and t2.fnumber like '01%' and a.fdetailid=t1.FDetailID) as 往来单位, case a.fdc when 1 then a.FAmount else 0 end DEBIT, case a.fdc when 0 then a.FAmount else 0 end credit, case a.FDC when 1 then 1 when 0 then -1 end as aFdc, f.fname as CurrencyName, a.* from DB20120712BZ..t_VoucherEntry as a left join DB20120712BZ..t_Account b on a.FAccountID =b.FAccountID left join DB20120712BZ..t_voucher c on a.FVoucherID=c.FVoucherID left join DB20120712BZ..t_VoucherGroup d on c.FGroupID=d.FGroupID left join DB20120712BZ..t_currency f on f.fcurrencyid=a.fcurrencyid where CHARINDEX ('收',d.FName)>0 and b.fnumber not like '1002%' and b.fnumber not like '1001%' union select (case when CHARINDEX ('收',d.FName)>0 then '收入' when CHARINDEX ('付',d.FName)>0 then '支出' end )as atype , case a.FDC when 0 then '贷' when 1 then '借' end as 借贷,b.FFullName, b.fname,b.FNumber,c.fyear,c.FPeriod, (select t2.ffullname from DB20120712BZ..t_ItemDetailv t1 left join DB20120712BZ..t_item t2 on t1.FItemClassID=t2.FItemClassID and t1.FItemID=t2.FItemID left join DB20120712BZ..t_itemclass t3 on t1.FItemClassID=t3.FItemClassID where t3.fname='往来单位' and t2.fnumber like '01%' and a.fdetailid=t1.FDetailID) as 往来单位, case a.fdc when 1 then a.FAmount else 0 end DEBIT, case a.fdc when 0 then a.FAmount else 0 end credit, case a.FDC when 1 then 1 when 0 then -1 end as aFdc, f.fname as CurrencyName, a.* from DB20120712BZ..t_VoucherEntry as a left join DB20120712BZ..t_Account b on a.FAccountID =b.FAccountID left join DB20120712BZ..t_voucher c on a.FVoucherID=c.FVoucherID left join DB20120712BZ..t_VoucherGroup d on c.FGroupID=d.FGroupID left join DB20120712BZ..t_currency f on f.fcurrencyid=a.fcurrencyid where CHARINDEX ('付',d.FName)>0 and b.fnumber not like '1002%' and b.fnumber not like '1001%' ) as k33 ) as TABLE1"}.ExecuteReader();
sqlDataReader2.Dispose();
SQL可以贴两页,报错可以到6000+行。
你确定不能用存储过程或者把部分功能封装成类?
这不是原程序,只是运行出错的地方,原本的代码是
SqlDataReader sqlDataReader2 = new SqlCommand
{
Connection = sqlConnection,
CommandText = sqltext }.ExecuteReader(); 这是用SQLServer clr 写的函数,sqltext 是clr 函数拼出来用来从多数据库获取数据的,sql 语句在SQLServer 是可以正常运行的