How to get return code of stored procedure

[bmoiroud]

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 @RC int
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] 
   @ProdCode
  ,@RecCode
  ,@TemplateLib1
  ,@TemplateLib2
  ,@TemplateLib3
GO

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

Thks

[UVS]

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.
MartinD