Capturing A Stored Procedure’s Return Value in VBA

Nice little primer on accessing stored procedure return values through ADODB in VBA.

Ramblings

Those of you who have ever done any serious programming with SQL Server stored procedures will know that they return an integer value to their caller on completion. The default value is ‘0’, which indicates success, and any other value indicates a failure at some stage in its processing. VBA has the power to capture these return values through the ADODB.Command object but, in order to do so, there are a few points you should consider.

1. Invoke Parameters.Refresh before Execute.
2. Specify adCmdStoredProc as the CommandType.
3. Specify an OLE DB provider but don't use MSDASQL.
4. If the Stored Procedure consists of more than one statement (most do) make sure it starts with SET NOCOUNT ON.
5. Poke/prod any returned, open recordset before examining the Return Value.
6. Use a TRY..CATCH block in your Stored Procedure to handle exceptions.
7. Use additional error handling in your VBA code.

View original post 1,012 more words

Advertisements
  1. Leave a comment

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: