原因：在SqlServer数据库中，若定义的存储过程中包含有insert/update等更新操作时，在设计器中对存储过程进行调用时，会提示没有返回数据集的错误。
解决方法：修改存储过程，在begin 后面添加 SET NOCOUNT ON。
不管多复杂的sql存储过程 只需要在第一个大的begin后面添加 如下：
ALTER proc [dbo].[proc_enterylist] @qybm varchar(50),@strWhere varchar(2000)=‘‘,@stratrow int,@pagesize int ,@total int out
as
begin
SET NOCOUNT ON /*在此处添加*/
declare @fcompnbr varchar(50)
--如果传进来的是[FCompanyId] ，则取 太乱。
if exists(select 1 from [QY_JBXX] where [FCompanyId] = @qybm) --如果传进来的是[FCompanyId] ，则取 太乱。
begin
select @fcompnbr = @qybm
end
--结束
else
begin
select @fcompnbr = FCompanyId from [dbo].[QY_JBXX] where QYBM = @qybm
end
create table #b(fnumber int ,fname varchar(200),forder dec(10,4) ,ftype varchar(50))
create table #t(id int identity,xm varchar(200) null,sfzh varchar(50) null,XB varchar(10) null,zslx varchar(200) null,zslxname varchar(2000) null,zsjb varchar(100) null,
zczsh varchar(100) null,zczy varchar(100) null,zsyxqjssj datetime null,fzsj datetime null,fzjg varchar(200) null,forder int null,rowspan int null,showname int null,showid int null)
insert into #b
select distinct fnumber,fname,forder,FType from CF_Dic_Person
where ftype =‘zcrylx‘ order by forder
insert into #b
select distinct fnumber,fname,forder/100,FType from CF_Dic_Person a
where ftype =‘qtlbry‘ and not exists(select 1 from #b b where a.fnumber = b.fnumber)
update #b set forder = 5000 where fnumber=407
if (@strWhere=‘‘)
begin
insert into #t(xm,zslx,sfzh,xb,zsjb,zczsh,zczy,zsyxqjssj,fzsj,fzjg,zslxname,forder)
select b.xm,b.zslx,a.sfzh,a.xb,b.zsjb,b.zczsh,b.zczy,case when b.zslx=407 and b.zsjb=115 then ‘2250-12-31‘ else isnull(b.zsyxqjssj,‘2250-12-31‘) end ,b.fzsj,b.fzjg,c.fname,c.forder
from RY_RYZSXX b left join RY_RYJBXX a ON A.rybh = B.rybh
left join #b c on b.zslx = c.fnumber
where b.zszt!=-1 and b.qybm [email protected]
order by sfzh,c.FOrder desc
end
else
begin
declare @strsql varchar(2000)
select @strsql = ‘
insert into #t(xm,zslx,sfzh,xb,zsjb,zczsh,zczy,zsyxqjssj,fzsj,fzjg,zslxname,forder)
select b.xm,b.zslx,a.sfzh,a.xb,b.zsjb,b.zczsh,b.zczy,case when b.zslx=407 and b.zsjb=115 then ‘‘2250-12-31‘‘ else isnull(b.zsyxqjssj,‘‘2250-12-31‘‘) end,b.fzsj,b.fzjg,c.fname,c.forder
from RY_RYZSXX b left join RY_RYJBXX a ON A.rybh = B.rybh
left join #b c on b.zslx = c.fnumber
where b.zszt!=-1 and b.qybm = ‘‘‘+ @fcompnbr + ‘‘‘
‘ + @strWhere + ‘
order by sfzh,c.FOrder‘
exec(@strsql)
end
update #t set forder = b.sumorder from #t a,(select sum(forder) sumorder,sfzh from #t group by sfzh) b where isnull(a.sfzh,0) = isnull(b.sfzh,0)
-- update #t set rowspan = b.rowspan from #t a,(select count(1) rowspan,sfzh from #t group by sfzh) b where isnull(a.sfzh,0) = isnull(b.sfzh,0)
-- update #t set showname = 1 from #t a,(select min(id) id,sfzh from #t group by sfzh) b where isnull(a.sfzh,0) = isnull(b.sfzh,0) and a.id = b.id
declare @shid table (id int identity,sfzh varchar(50) null)
insert into @shid(sfzh) select sfzh from (select sfzh,max(forder) forder,min(id) id from #t group by sfzh) dd order by forder desc,id
update #t set showid = b.id from #t a,@shid b where isnull(a.sfzh,0) = isnull(b.sfzh,0)
select @total = count(1) from #t
select top (@pagesize) * into #output from (SELECT ROW_NUMBER() OVER(ORDER BY showid) AS RowNumber, * from #t) aa where RowNumber > @stratrow
--翻页重新计算
update #output set rowspan = sl from #output a ,(select sfzh,count(1) as sl from #output group by sfzh) b where isnull(a.sfzh,0) = isnull(b.sfzh,0)
update #output set showname = 1 from #output a ,(select sfzh,min(id) id from #output group by sfzh) b where isnull(a.sfzh,0) = isnull(b.sfzh,0) and a.id = b.id
select * from #output ORDER BY showid
select (select count(1) from #t a) as zssum ,count(1) sl,zslxname,zslx from #t c group by zslxname,zslx
end
GO
通过在数据库查询处调用存储过程
若存储过程已经定义好，不希望再修改，这时可以通过模板数据集>数据库查询处调用存储过程，并在调用前增加SET NOCOUNT ON，最后执行的语句为：
SET NOCOUNT ON
exec proc_enterylist‘${qybm}‘,‘‘,0,1,20
注：Oracle数据库中不存在这样的问题，只要成功创建一个存储过程，在设计器中正常调用即可，不会出现类似的问题。