How to get return code of stored procedure

General discussion forum about all databases other than SqlBase.

How to get return code of stored procedure

Post by bmoiroud » 01 Jul 2010, 17:16

I am using OLE DB to connect to SQL Server 2008
I want the return code of stored procedure

In T-SQL syntax is like this :

Code: Select all

DECLARE @ProdCode nvarchar(15)
DECLARE @RecCode nvarchar(2)
DECLARE @TemplateLib1 nvarchar(80)
DECLARE @TemplateLib2 nvarchar(80)
DECLARE @TemplateLib3 nvarchar(80)

EXECUTE @RC = [dbo].[sp_CreateTemplateFromRec] 

in TD i use the following code

Code: Select all

Call SqlConnect( hlSql )
Call SqlPrepareSP( hlSql, 'sp_CreateTemplateFromRec  :spRecProdCodeRef, :spRecCodeRef, :slRecTempLib1, :slRecTempLib2, :slRecTempLib3 ', ':nlReturnCode')
Call SqlExecute( hlSql )
The stored procedure is working but nlReturnCode doesn't change

What is the correct syntaxe to get the correct value of nlReturnCode



Re: How to get return code of stored procedure

Post by UVS » 06 Jul 2010, 22:53

I think you need to set the variables that nlReturnCode uses.

From the Help file:
SqlPrepareSP (hSql, strStoredProc, strIntoList)

strIntoList - String variable that contains the Into variables for any result set generated by the Stored Procedure. If the caller knows that the stored procedure does not generate any result set, this can be set to strNull. If passed, this should be a comma-separated list of variables and precede each variable name with a colon.

Then read those variables after your SqlExecute and SqlFetchNext's.

Code: Select all

Set strSQLCommand = 'DECLARE @Error INT
      EXEC TKC_ViewOpenTime2 0, ' || SalNumberToStrX( nEmpNmbr, 0 ) || ', ' || strGlobalRemoteServer || ', ' || SalNumberToStrX( nPayPeriodNow, 0) || ', @ErrorSave = @Error OUTPUT'
Set strIntoList = ':dtWorkDate, :strAccountCode, :nHoursWorked, :strStartTime, :strEndTime, :nShift, :strAccountCodeID, :strWorkID, :nApproved'
Call SqlConnect( hSqlGetTime) 
If SqlPrepareSP(  hSqlGetTime, strSQLCommand, strIntoList )
   If SqlExecute (hSqlGetTime)
      While SqlFetchNext ( hSqlGetTime, nInd )
                                        YourCode HERE!!!!!
Otherwise we need a small test case that isn't working for you.

Return to “General Discussion”

Who is online

Users browsing this forum: [Ccbot] and 1 guest