Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 778
  • Last Modified:

how do you get a return code from an extened stored procedure for example

I want to convet this to delphi code, but I want to get the @res value in this case, can anyone provide an example

declare @res as  integer
exec @res=sp_who
print @res

thanks in advance

Ian
0
alchemy9
Asked:
alchemy9
  • 7
  • 4
  • 4
  • +2
1 Solution
 
kretzschmarCommented:
use a adoStoredProc

set there your adoconnection and assign your procedurename
in the associated property (sp_who in your case)

usual the output-parameter will automatically added in the parameters

in code use

begin
  adostoredproc.execproc;
  yourResult := adostoredproc.parameters[0].Value; //or AsInteger
end;

hope this helps

meikl ;-)
0
 
Ivanov_GCommented:
ADOStoredProc1.FieldByName('RES').AsInteger
0
 
kretzschmarCommented:
>ADOStoredProc1.FieldByName('RES').AsInteger
in case of using the open-method
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
alchemy9Author Commented:
I ran the following code and got an error list index out of bounds(0)

var
RES1 : Variant;
begin
    adostoredproc1.ProcedureName:='sp_who';
    adostoredproc1.ExecProc;
   res1 := ADOStoredProc1.Parameters[0].value
end;

the asinteger is not displayed as an option- I would prefer to get this back as an intger if possible

res1 := ADOStoredProc1.Parameters[0].asoption


thanks

Ian
0
 
mokuleCommented:
You can get values from procedure in this way

  with DM2.ADOCmd do
    begin
    CommandText := 'ArchiwParam';
    Parameters.Refresh;
// parameters passed to procedure
    Parameters.ParamByName('@nazwabazy').Value := AppTitle;
    Parameters.ParamByName('@kompldst').Value := '';
    Parameters.ParamByName('@kompldt').Value := Now;
    Parameters.ParamByName('@rozdst').Value := '';
    Parameters.ParamByName('@rozdt').Value := Now;
    Prepared := True;
    Execute;
// paramter get from procedure
    Edit2.Text := Parameters.ParamByName('@kompldst').Value;

stored procedure declaration

CREATE PROCEDURE ArchiwParam  @nazwabazy varchar(40),      
                               @kompldt datetime OUTPUT,      
                               @kompldst varchar(200) OUTPUT,
                               @rozdt datetime OUTPUT,
                               @rozdst varchar(200) OUTPUT


// this will be parameter number 0
RETURN @@IDENTITY
0
 
Ivanov_GCommented:

  var
     Res : Integer;
  begin
     ADOStoredProc1.ProcedureName := 'sp_who';
     ADOStoredProc1.Open;
     Res := ADOStoredProc1.FieldByName('RES').AsInteger;
  end;
0
 
mokuleCommented:
1. Your procedure doesn't return desired value as parameter 0
2. To return in @res declare it as OUTPUT
3. Do Paramters.Refresh
0
 
kretzschmarCommented:
>I ran the following code and got an error list index out of bounds(0)

check if a parameter named return_value (default name) is automatically added into the parameterlist

if you set all at runtime, you should check if this parameter is automatically added, otherwise you have to add byself

meikl ;-)
0
 
alchemy9Author Commented:
mokule,

thanks for the example, that shows how to get the output paramters, but with extended stored procedures you can have a return code in addtion to the Result set, that was what I was trying to show in the SQL code above.

I want to first check the return code and then if appropriate check the result set.

so the psuedo code would be

ADOStoredProc1.ProcedureName := 'sp_who';

check the return code 0 (success) or 1 (failure)  --- see the ms books on line for further info.

   if return code is 0 then parse the result set...

else

   flag error


so the problem firstly to get the return code...


sorry for the confusion.
0
 
_Katka_Commented:
Hi, return parameter is stored in ADOStoredProc1.Params[0].AsInteger after the execution :)

Kate
0
 
mokuleCommented:
I achieved satisfactonary results with this.
I've tried with sp_who

      for i := 1 to ADOStoredProc1.Parameters.Count - 1 do
        begin
        ADOStoredProc1.Parameters[i].Value := StringGrid1.Cells[1,i];
        end;

      ADOStoredProc1.Prepared := True;
      try
        ADOStoredProc1.ExecProc;
        StringGrid1.Cells[1,0] := ADOStoredProc1.Parameters[0].Value;
      except
        end;
0
 
mokuleCommented:
BTW
This was executed before.

  ADOStoredProc1.Active := False;
  ADOStoredProc1.ProcedureName := ListBox3.Items[ListBox3.ItemIndex];
  ADOStoredProc1.Parameters.Refresh;
  StringGrid1.RowCount := ADOStoredProc1.Parameters.Count;
  for i := 0 to ADOStoredProc1.Parameters.Count - 1 do
    begin
    StringGrid1.Cells[0,i] := ADOStoredProc1.Parameters[i].Name;
    StringGrid1.Cells[1,i] := '';
    end;
0
 
alchemy9Author Commented:
If I enter the proceudure name as sp_who in the object inspector the following code works


adostoredproc1.ExecProc;
   SHOWMESSAGE(ADOStoredProc1.Parameters[0].VALUE);

but flags an error if I use the following code, not defining sp_who in the object inspector

    adostoredproc1.ProcedureName:='sp_who';
    adostoredproc1.ExecProc;
   SHOWMESSAGE(ADOStoredProc1.Parameters[0].VALUE);

what do I need to do
0
 
mokuleCommented:
Have You tried this. From my code.
Everything was done at run time.

  ADOStoredProc1.Parameters.Refresh;
0
 
_Katka_Commented:
The execution is following:

with ADOStoredProcedureWhatever do
begin
  StoredProcName:=ProcName; // redirection of component to desired procedure
  Prepare;                               //  retrieve requested parameter list (this is what you need, after prepare you'll be able to see parameters live :)
  Params[0].Value:=Value0;     //  fill-in parameters
  Params[1].Value:=Value1;     //  etc.
  ExecProc;                             //  in a case the stored procedure doesn't contain any select
or
  Open;                                  //  when stored procedure contains 1 or more result dataset (and when there's need for it/them)
  Result:=Params[0].Value;     //  returned value
end

Kate
0
 
mokuleCommented:
This should be Your code.

   adostoredproc1.ProcedureName:='sp_who';

   adostoredproc1..Parameters.Refresh;    // it is important

   ADOStoredProc1.Parameters[1].AsString := 'sa';
//or better
// ADOStoredProc1.Parameters.ParamByName('@loginame').Value := 'sa';

   try
     adostoredproc1.ExecProc;
     SHOWMESSAGE(ADOStoredProc1.Parameters[0].VALUE);
   except
     end;
0
 
_Katka_Commented:
Specifically for ADO component it's rather:

with ADOStoredProcedureWhatever do
begin
  ProcedureName:=ProcName;        // redirection of component to desired procedure
  Prepared:=True;                         //  retrieve requested parameter list (this is what you need, after prepare you'll be able to see parameters live :)
  Parameters[0].Value:=Value0;     //  fill-in parameters
  Parameters[1].Value:=Value1;     //  etc.
  ExecProc;                                   //  in a case the stored procedure doesn't contain any select
or
  Open;                                        //  when stored procedure contains 1 or more result dataset (and when there's need for it/them)
  Result:=Parameters[0].Value;     //  returned value
end

Kate
0
 
alchemy9Author Commented:
Mokule,

you star,

I was missing the adostoredproc1.parameters.refresh  part this now works

thank you all for you rapid response to the question.

Katka,

I tried prepared:= true , but this still gave the error, the parameters.refresh was the item needed.
0
 
mokuleCommented:
  adostoredproc1..Parameters.Refresh;    // it is important
It is important to execute it not to have two dots int it :)
should be of course
   adostoredproc1.Parameters.Refresh;
0
 
kretzschmarCommented:
:-((
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 7
  • 4
  • 4
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now