服务器上有下面存储过程CREATE PROCEDURE sp_GnzGnml
@GnzID varchar(8)='',
@RecCount int =0 OUTPUT
ASselect b.GnID,b.Gnmc,b.GnCode from GnzToGnml a,Gnml b where a.gnmlID=b.gnID and a.GnzID=@GnzID
set @RecCount=@@RowCount
我想在客户端同时获得结果集及@RecCount参数的返回值。 用TQuery组件各TStoredProc分别如何实现?
procedure TPropertyForm.FindGnzGnml(GnzID: string);
var param: TParam;
begin
with SystemDataModule.spGnzGnml do {sp_GnzGnml 是一个TStoredProc}
begin
Param:=Params.CreateParam(ftString,'@GnzID',ptInput);
Param.Value:=GnzID;
Param:=Params.CreateParam(ftString,'@RecCount',ptOutput);
Param.Value:=0;
showmessage(GnzID);
ExecProc;
if Params.ParamByName('@RecCount').Value=0 then
showmessage('no record')
else
showmessage('have record');
end;
end;
将ExecProc改为OPen,会返回错误:Error Creating Cursor Handle,何解?用TStoredProc能否返回结果集?
@GnzID varchar(8)='',
@RecCount int =0 OUTPUT
ASselect b.GnID,b.Gnmc,b.GnCode from GnzToGnml a,Gnml b where a.gnmlID=b.gnID and a.GnzID=@GnzID
set @RecCount=@@RowCount
我想在客户端同时获得结果集及@RecCount参数的返回值。 用TQuery组件各TStoredProc分别如何实现?
procedure TPropertyForm.FindGnzGnml(GnzID: string);
var param: TParam;
begin
with SystemDataModule.spGnzGnml do {sp_GnzGnml 是一个TStoredProc}
begin
Param:=Params.CreateParam(ftString,'@GnzID',ptInput);
Param.Value:=GnzID;
Param:=Params.CreateParam(ftString,'@RecCount',ptOutput);
Param.Value:=0;
showmessage(GnzID);
ExecProc;
if Params.ParamByName('@RecCount').Value=0 then
showmessage('no record')
else
showmessage('have record');
end;
end;
将ExecProc改为OPen,会返回错误:Error Creating Cursor Handle,何解?用TStoredProc能否返回结果集?
create proc test(@i int,@count int output)
as
begin
--创建临时表
create table #t(a int ,b int ,constraint Pk_t primary key (a) )
insert into #t select 0,1
union all select 1,2
select * from #t where a = @i
set @count = @@rowcount enddelphiprocedure TForm1.Button1Click(Sender: TObject);
begin
with ADOStoredProc1 do
begin
close;
Parameters.Refresh;
Parameters.ParamByName('@i').Value:=0;
Parameters.ParamByName('@count').Value:=0;
Open; //返回存储过程select的结果集 用dbgrid连接就知道了
// ExecProc; 不返回select的结果集
if Parameters[0].Value=0 then showmessage('01'); //参数return_value 返回0代表存储过程返回成功
if Parameters[2].Value = 1 then showmessage('21'); //输出参数@count 返回行数
endend;
begin
with ADOStoredProc1 do
begin
close;
Parameters.Refresh;
parameters.parambyname('@GnzID').Value:=GnzID;
Parameters.ParamByName('@count').Value:=0; 随便也可为'' Open; //返回存储过程select的结果集 用dbgrid连接就知道了
// ExecProc; 不返回select的结果集
if Parameters[0].Value=0 then showmessage('01'); //参数return_value 返回0代表存储过程返回成功
if Parameters[2].Value = 1 then showmessage('21'); //输出参数@count 返回行数
endend;
使用ExecProc,则不限制,不返回结果,多用纯执行性过程。