We help IT Professionals succeed at work.

Return value from SQL stored procedure

DARRAH
DARRAH asked
on
Medium Priority
1,269 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.
Comment
Watch Question

Commented:
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.

Commented:
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.

Author

Commented:
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.

Commented:
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
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Thanks mayhew!
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.