Solved

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

Posted on 2004-04-29
20
691 Views
Last Modified: 2010-04-05
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
Comment
Question by:alchemy9
  • 7
  • 4
  • 4
  • +2
20 Comments
 
LVL 27

Expert Comment

by:kretzschmar
ID: 10946967
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
 
LVL 12

Expert Comment

by:Ivanov_G
ID: 10946974
ADOStoredProc1.FieldByName('RES').AsInteger
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 10946982
>ADOStoredProc1.FieldByName('RES').AsInteger
in case of using the open-method
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:alchemy9
ID: 10947105
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
 
LVL 17

Expert Comment

by:mokule
ID: 10947117
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
 
LVL 12

Expert Comment

by:Ivanov_G
ID: 10947128

  var
     Res : Integer;
  begin
     ADOStoredProc1.ProcedureName := 'sp_who';
     ADOStoredProc1.Open;
     Res := ADOStoredProc1.FieldByName('RES').AsInteger;
  end;
0
 
LVL 17

Expert Comment

by:mokule
ID: 10947140
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
 
LVL 27

Expert Comment

by:kretzschmar
ID: 10947163
>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
 

Author Comment

by:alchemy9
ID: 10947236
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
 
LVL 10

Expert Comment

by:_Katka_
ID: 10947470
Hi, return parameter is stored in ADOStoredProc1.Params[0].AsInteger after the execution :)

Kate
0
 
LVL 17

Expert Comment

by:mokule
ID: 10947511
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
 
LVL 17

Expert Comment

by:mokule
ID: 10947523
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
 

Author Comment

by:alchemy9
ID: 10947561
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
 
LVL 17

Expert Comment

by:mokule
ID: 10947595
Have You tried this. From my code.
Everything was done at run time.

  ADOStoredProc1.Parameters.Refresh;
0
 
LVL 10

Expert Comment

by:_Katka_
ID: 10947645
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
 
LVL 17

Accepted Solution

by:
mokule earned 500 total points
ID: 10947683
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
 
LVL 10

Expert Comment

by:_Katka_
ID: 10947739
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
 

Author Comment

by:alchemy9
ID: 10947805
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
 
LVL 17

Expert Comment

by:mokule
ID: 10947823
  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
 
LVL 27

Expert Comment

by:kretzschmar
ID: 10947838
:-((
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction Raise your hands if you were as upset with FireMonkey as I was when I discovered that there was no TListview.  I use TListView in almost all of my applications I've written, and I was not going to compromise by resorting to TStringGrid…
In my programming career I have only very rarely run into situations where operator overloading would be of any use in my work.  Normally those situations involved math with either overly large numbers (hundreds of thousands of digits or accuracy re…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question