Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2004-04-29
20
Medium Priority
?
752 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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 2000 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 to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

Have you ever had your Delphi form/application just hanging while waiting for data to load? This is the article to read if you want to learn some things about adding threads for data loading in the background. First, I'll setup a general applica…
Hello everybody This Article will show you how to validate number with TEdit control, What's the TEdit control? TEdit is a standard Windows edit control on a form, it allows to user to write, read and copy/paste single line of text. Usua…
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Suggested Courses

971 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