B4J Question [SOLVED] MS SQL Stored procedure - return value

[fprobst]

Hello, I am using a MS SQL Server an useing the library "SQLCallPRoc Version 1-10" from keirS
I have the following stored procedure:

CREATE procedure [dbo].[sp_TMS_BuchenPersKommt](
    @cPersNr  char(10),
    @dDatum   AMSDate,
    @dZeit    AMSTime,
    @cGrund   char(1)
    ) as

begin

set nocount on
-- cursor variablen
declare @nReturn    numeric(2)
set @nReturn    = 0

-- In der Stempeldatei nach einem gültigen Anwesend-Satz suchen
IF EXISTS (select FEHLER from [dbo].[TMSD230] where PERSNR = @cPersNr and STATUS != 0 and FEHLER = 0)
   BEGIN
   SET @nReturn = 1
   update [dbo].[TMSD230] set FEHLER = 1 where PERSNR = @cPersNr and STATUS != 0 and FEHLER = 0
   END
-- Einen Satz erzeugen
INSERT INTO [dbo].[TMSD230] (PERSNR, ANDAT, ANZEIT, STATUS, GRUND) VALUES (@cPersNr, @dDatum, @dZeit, 1, @cGrund)

return @nReturn

end

I want to use the return value @return. But when I try to create an output parameter with the function "MSSQLProcedure.AddOutputParameter (5, MSSQLProcedure.SQLINTEGER)" I get only the error message that the value 5 is outside the valid range. How do I have to change my procedure or do I make a mistake when calling the function? Can someone help me there?

    Dim MyResults As Map
    Dim MyResultSet As ResultSet
    Dim MSSQLProcedure As CallProc
    '    'Set the procedure to call
    MSSQLProcedure.ProcedureCall = "{call dbo.sp_TMS_BuchenPersKommt(?, ?, ?, ?)}"
    '    'Register Input Parameters
    MSSQLProcedure.AddInputParameter(1,MSSQLProcedure.SQLCHAR,"131") 
    MSSQLProcedure.AddInputParameter(2,MSSQLProcedure.SQLDATE,"2019-10-19") 
    MSSQLProcedure.AddInputParameter(3,MSSQLProcedure.SQLCHAR,"2000-01-01 10:12:30.000") 
    MSSQLProcedure.AddInputParameter(4,MSSQLProcedure.SQLCHAR,"1") 
     'Register Output Parameters
    MSSQLProcedure.AddOutputParameter(5,MSSQLProcedure.SQLINTEGER)
    MSSQLProcedure.ExecProcedure(sqlMS)

    
    MyResultSet = MSSQLProcedure.FetchResultSet


    MyResults =  MSSQLProcedure.FetchOutputParameters
    Log(MyResults)
    MSSQLProcedure.close

 

[rraswisak]

You must add @nReturn as OUTPUT parameter on the procedure rather than declaring inside of begin...End

CREATE procedure [dbo].[sp_TMS_BuchenPersKommt](
    @cPersNr  char(10),
    @dDatum   AMSDate,
    @dZeit    AMSTime,
    @cGrund   char(1),
    @nReturn numeric(2) OUTPUT

    ) as

begin

set nocount on
-- cursor variablen
-- declare @nReturn    numeric(2) <--- this line not necessary
set @nReturn    = 0

-- In der Stempeldatei nach einem gültigen Anwesend-Satz suchen
IF EXISTS (select FEHLER from [dbo].[TMSD230] where PERSNR = @cPersNr and STATUS != 0 and FEHLER = 0)
   BEGIN
   SET @nReturn = 1
   update [dbo].[TMSD230] set FEHLER = 1 where PERSNR = @cPersNr and STATUS != 0 and FEHLER = 0
   END
-- Einen Satz erzeugen
INSERT INTO [dbo].[TMSD230] (PERSNR, ANDAT, ANZEIT, STATUS, GRUND) VALUES (@cPersNr, @dDatum, @dZeit, 1, @cGrund)

--return @nReturn

end

Sorry, not tested

 

[fprobst]

Hello rraswisak, thanks for your help.
Something like that I've already tried it, but I forgot to adjust the procedure call.

Old:

MSSQLProcedure.ProcedureCall = "{call dbo.sp_TMS_BookPersComing (?,?,?,?)}"

New:

MSSQLProcedure.ProcedureCall = "{call dbo.sp_TMS_BookPersComing (?,?,?,?,?)}"   <---- add one Parameter

Now everything works. Thanks.