Solved

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

Posted on 2004-04-29
20
680 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
 

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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Creating an auto free TStringList The TStringList is a basic and frequently used object in Delphi. On many occasions, you may want to create a temporary list, process some items in the list and be done with the list. In such cases, you have to…
Introduction I have seen many questions in this Delphi topic area where queries in threads are needed or suggested. I know bumped into a similar need. This article will address some of the concepts when dealing with a multithreaded delphi database…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

746 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now