调用存储过程

调用存储过程

1）创建一个sqlserver存储过程

alter PROCEDURE gettables @sum INT OUTPUT
AS
BEGIN
  SET @sum = 1 + 2
  select top 2 * from tunit
  select top 2 * from tgoods
END
GO

　　

　　2）中间件调用存储过程

unit api.storedproc;
//cxg 2024-5-26
{$mode delphi}

interface

uses DB.storedproc, sys.global,
  fpjson, jsonparser, uni,
  SysUtils, Classes, DB;

type

  { Tstoredproc }

  Tstoredproc = class(Trpc)
    procedure Open(ctxt: Tcontext);  //多个结果集
    procedure open2(ctxt: Tcontext); //单个结果集
  end;

implementation

{ Tstoredproc }

procedure openInParam(sp: TUniStoredProc; param: tjsonobject);
begin
//
end;

procedure openOutParam(sp: TUniStoredProc; param: tjsonobject);
begin
  if sp.FindParam('sum') <> nil then
    param.Add('sum', sp.ParamByName('sum').AsInteger);
end;

procedure Tstoredproc.Open(ctxt: Tcontext);
var
  sp: Tsp;
begin
  sp.dbid := '1';
  sp.ctxt := ctxt;
  sp.procName := 'gettables';
  sp.onInParam := openInParam;
  sp.onOutParam := openOutParam;
  sp.Open;
end;

procedure open2param(sp: TUniStoredProc; param: tjsonobject);
begin
  sp.ParamByName('goodsid').AsWideString := param.Get('goodsid');
end;

procedure open2outParam(sp: TUniStoredProc; param: tjsonobject);
begin
  //
end;

procedure Tstoredproc.open2(ctxt: Tcontext);
var
  sp: Tsp;
begin
  sp.dbid := '1';
  sp.ctxt := ctxt;
  sp.procName := 'sp_9';
  sp.onInParam := open2param;
  sp.onOutParam:=open2outParam;
  sp.Open2;
end;

initialization
  RegisterClass(Tstoredproc);    //注册类

end.

　　3）客户端调用存储过程

3.1）返回多个结果集并返回output参数值

　

3.2）返回单个结果集带input参数