Solved

Return value from SQL stored procedure

Posted on 1998-07-14
6
847 Views
Last Modified: 2010-04-04
I'm running calls to SQL Server stored procedures using Delphi's Stored procedure component. In it, there is a RETURN_VALUE parameter which does contain a value regardless what I might assign it in the SQL Server stored procedure. For example, in my SQL Server stored procedure, I make several SQL commands, update, insert, etc. At any point, the command might fail, ie, with an insert, the key might already exist. At this point, the stored procedure just stops and I don't know where it ended. I can't override the message that SQL Server displays, although I've tried with RaiseError and my own message, and I can't seem to retrieve a RETURN_VALUE to indicate where in the SQL stored procedure it failed. I need some feedback on this.
0
Comment
Question by:DARRAH
  • 3
  • 2
6 Comments
 
LVL 8

Expert Comment

by:ZifNab
ID: 1357869
Hi DARRAH,

Do you use it like this :

Using Stored Procedures

                         Question:

                         How do I use stored procedures in a multi-tier environment?

                         Answer:

                         You'll need to get the provider interface and set the
                         parameters before trying to open the client dataset.
                         Unfortunately there is no way to do this at design time,
                         but it should work OK at run time.  Here is some code that
                         shows basically what you need to do:

                              procedure TDBClientTest.Button3Click(Sender: TObject);
                              var
                                Params: Variant;
                              begin
                                ClientData.Provider := RemoteServer1.GetProvider('ProviderName');
                                Params := VarArrayCreate([0, 1], varVariant);
                                Params[0] := VarArrayOf(['@InParam1', 101]);
                                Params[1] := VarArrayOf(['@InParam2', 'test_param']);
                                ClientData.Provider.SetParams(Params);
                                ClientData.Open;
                              end;

                         This example assumes the stored procedure takes 2
                         parameters, and binds them by name.   If you need to
                         update the data returned from a stored procedure, you
                         will need to use a TUpdateSQLProvider.

From Q&A borland.
Zif.
0
 
LVL 5

Expert Comment

by:mayhew
ID: 1357870
You set the return value by just issuing a select in your stored proc.

For example if you have a TDBGrid pointing to a TDataSource that points to a TStoredProc and your last line of the stored proc is 'select * from table', your grid will be populated with the results of that query (which is what's in your return value).

Using this, you can return anything you want (result set, error code, etc.)

Try the following:

Define the following variables:
@ERR_CODE int,
@SECTION int

After each call (insert, update, whatever) have the following code:
Select @SECTION = 1                               --Designate a new number for each section.
Select @ERR_CODE = @@ERROR
if @err_code <> 0 GOTO EXIT1  

At the end of your stored proc, have the following.
EXIT1:
if @ERR_CODE = 0    
  select * from table    --whatever to populate your grid (or other data-aware components).
  else
  select @SECTION


This will return a result set if there are not errors otherwise it will return the section number you assigned.

Let us know if this works for you.
0
 

Author Comment

by:DARRAH
ID: 1357871
Setting a section variable to the area I'm in makes sense. I'll set it before I run the INSERT or DELETE commands so I'll know what was the last thing to work. However, I've notice that when the INSERT or DELETE command does fail for whatever reason, it just kicks out. I'm not even able to grab the error code or change the error message that SQL Server generates (and which will mean absolutely nothing to the end user) and replace it with one of mine. That'll take care of my problem when I can do that.
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 5

Expert Comment

by:mayhew
ID: 1357872
Darrah,

This sounds strange to me.  Is there any way you could post your stored proc or perhaps e-mail it to me so I could take a look at it?

Also, how do you have your stored proc component set up?  What did you enter for DatabaseName, Params, etc.?

donm@maccnet.com
0
 
LVL 5

Accepted Solution

by:
mayhew earned 100 total points
ID: 1357873
Ah.  Thanks for sending me your code.  I think I see the problem.  You are using RETURN to send back an error code.  
In SQL Server a stored proc is actually a function that returns an integer code on completion.  RETURN immediately stops execution of your proc and specifies the return code.  I've found this to be more useful on the SQL Server side of things (like calling a stored proc from a stored proc and using the return code to tell me the status of the inner proc).

That's not say you can't use that value.  I don't know how you're doing it but we always do it on the fly as follows (keep in mind that this is a snippet taken out of context - but it is representative of code that I use):

  With spStoredProc do
    Params.CreateParam(ftInteger,'RETURN_VALUE',ptResult);
    Try
      Prepare;
      ExecProc;
      Case ParamByName('RETURN_VALUE').AsInteger Of
        0 : (*Return code 0 means successful completion*);
        98: (*user defined codes*);
        99: MessageDlg(*some error message*);

In my opinion, it's more useful to use a select statement in place of the return.

Hence, replace
  RETURN @RetCode
with
  select @RetCode as ReturnCode

You then do a ParamByName on 'ReturnCode' (if it exists) and you can produce any Delphi response that you desire.

I'm not sure if this will fit into your app properly but for testing purposes it should give you the right idea of what's going on.

Also, RaisError is largely in internal message for SQL Server.  As I understand it, RaisError is used to set the value for RETURN as well as feed error information into the sysmessages table.  

I hope this helpful
0
 

Author Comment

by:DARRAH
ID: 1357874
Thanks mayhew!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Delphi XE10, DigitalPersona Fingerprint reader and MySQL 6 216
PDF library for Delphi 2 104
delphi prevent click fast 2 189
Delphi application Soap connection 5 96
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…
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…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…
Need to grow your business through quality cloud solutions? With everything required to build a cloud platform and solution, you may feel like the distance between you and the cloud is quite long. Help is here. Spend some time learning about the Con…

914 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

19 Experts available now in Live!

Get 1:1 Help Now