SProcCall.getReturnValue() vs SProcCall.getOutParamValue(index OR name)

Ignition
1

I have an Ignition script that contains this…

name = “example”
ID = 1
getLID = system.db.createSProcCall("GetLIDByIDName", "Ignition")
getLID.registerReturnParam(system.db.INTEGER) #registerOutParam("LID", system.db.INTEGER)
getLID.registerInParam("name", system.db.VARCHAR, name)
getLID.registerInParam("ID", system.db.INTEGER, ID)
system.db.execSProcCall(getLID)
LID = getLID.getReturnValue() #LID = getLID.getOutParamValue()

and a store procedure that looks like this…

CREATE PROCEDURE [dbo].[GetLIDByIDName]
@ID AS int,
@name AS varchar
AS

DECLARE @LID int

SELECT @LID = [LID] FROM [Ignition].[dbo].[LTable]
WHERE [ID] = @ID
AND [Name] = @name

RETURN @LID

Instead of getting the correct value in LID, I always get 0 (I have tested the stored procedure and it does give values other than 0). So, I think maybe I should be using what I have commented above in the script. But I am not really sure what the difference is between getReturnValue and getOutParamValue. Can someone explain the difference between SProcCall.getReturnValue() and SProcCall.getOutParamValue(index OR name) and in what scenario each one should be used? Please share if you see anything else that might be causing this too, thanks.

2

Assuming this is MSSQL, which is what it looks for. You haven't configured the @LID parameter in the stored proc to be an output parameter. That would look like:

DECLARE @LID INT OUTPUT

That is where you would use the SProcCall.getOutParamValue().

Otherwise, the SProcCall.getReturnValue() should return whatever is actually returned with the RETURN keyword.

I will say that I have had very little luck with the SProc functions, and for something that is as simple as what you have here, I would definately recomend just using a Named Query witht he following query.

Select [LID] from LTable
WHERE [ID] = :ID AND [Name] = :Name
1 Like
3

The issue with getting 0 returned every time was from not specifying the number of characters for the name varchar in the stored procedure. It was set to 1, I guess by default, and I was passing in strings with more than 1 character