Finereport report, using SQL stored procedures with parameters to report errors that do not return a dataset

Cause: In SQL Server database, when you define a stored procedure that contains an update operation such as Insert/update , you are prompted to make a call to the stored procedure in the designer without the error returning the dataset.

Workaround: Modify the stored procedure and add SET NOCOUNT on after begin.

No matter how complex a SQL stored procedure is, simply add the following after the first big begin:

ALTER proc [dbo]. [Proc_enterylist] @qybm varchar, @strWhere varchar (+) = ", @stratrow int, @pagesize int, @total int outasbegin SE T NOCOUNT ON/* Add here */declare @fcompnbr varchar (50)-if it comes in [Fcompanyid], take it too messy. if exists (select 1 from [qy_jbxx] where [fcompanyid] = @qybm)--if it comes in [Fcompanyid], it is too messy. Begin Select @fcompnbr = @qybm End--end ELSE BEGIN Select @fcompnbr = Fcompanyid from [dbo]. [QY_JBXX] where QYBM = @qybm end CREATE TABLE #b (fnumber int, fname varchar ($), Forder Dec (10,4), ftype varchar (50)) CREATE table #t (ID int identity,xm varchar (NULL,SFZH) NULL,XB varchar () null,zslx varchar (NULL,ZSLX) Name varchar NULL,ZSJB varchar (+) NULL, zczsh varchar (null,zczy) varchar NULL,ZSYXQJSSJ da Tetime NULL,FZSJ datetime NULL,FZJG varchar ($) null,forder int null,rowspan int null,showname int null,showid int null) I Nsert to #b select distinct Fnumber,fname,forder,ftype from Cf_dic_person where FType = ' ZCRYLX ' ORDER by Forderinsert to #b select distinct Fnumber,fname,forder/100,ftype from Cf_dic_person a where Ftyp E = ' Qtlbry ' and NOT EXISTS (select 1 from #b b where a.fnumber = b.fnumber) Update #b Set forder = the Where fnumber=4 if (@strWhere = ") begin INSERT into #t (Xm,zslx,sfzh,xb,zsjb,zczsh,zczy,zsyxqjssj,fzsj,fzjg,zslxname,forder) Sele CT 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 joins 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 de SC end ELSE BEGIN declare @strsql varchar SELECT @strsql = ' INSERT INTO #t (XM,ZSLX,SFZH,XB,ZSJB,ZCZSH,ZCZY,ZSYXQJSS J,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= 2250-12-31 ' Else IsNull (b.zSYXQJSSJ, ' 2250-12-31 ') End,b.fzsj,b.fzjg,c.fname,c.forder from Ry_ryzsxx B left joins 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,SF ZH from #t GROUP by Sfzh) b where IsNull (a.sfzh,0) = IsNull (b.sfzh,0)--Update #t Set rowspan = B.rowspan from #t A, (Selec T count (1) Rowspan,sfzh from #t Group by Sfzh) b where IsNull (a.sfzh,0) = IsNull (b.sfzh,0)--Update #t set showname = 1 fr Om #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 Decla Re @shid table (id int identity,sfzh varchar () null) INSERT INTO @shid (SFZH) Select Sfzh from (select Sfzh,max (Forder) F Order,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 whe Re IsNull (a.sfzh,0) = isNull (b.sfzh,0) Select @total = count (1) from #t select Top (@pagesize) * to #output from (select Row_number () over (ORD ER by Showid) as RowNumber, * from #t) AA where RowNumber > @stratrow--page recalculation update #output set rowspan = SL from #o Utput A, (select Sfzh,count (1) as SL from #output Group by Sfzh) b where IsNull (a.sfzh,0) = IsNull (b.sfzh,0) Update #outpu T set ShowName = 1 from #output A, (select Sfzh,min (ID) ID from #output Group by Sfzh) b where IsNull (a.sfzh,0) = Isnu LL (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) s L,ZSLXNAME,ZSLX from #t C GROUP by Zslxname,zslxendgo

Call the stored procedure at the database query

If the stored procedure is already defined and does not want to be modified, At this time can pass template DataSet > database query Call the stored procedure and add set NOCOUNT on

' ${QYBM} ',',0, 1,

Note: No such problem exists in the Oracle database, as long as a stored procedure is successfully created and is normally called in the designer, no similar problem occurs.

Finereport report, using SQL stored procedures with parameters to report errors that do not return a dataset