SQL Stored Procedure Parameters

Ignition
1

Hello

I have a stored procedure in SQL Server 2008. I am using the Ignition version 7.5.5 b1255

sp_AddValues
@ID AS int,
@val1 AS int = null,
@val2 AS int = null

INSERT SomeTable (ID, val1, val2)
VALUES (@ID, val1, val2)

I try running the following through script.
call=system.db.createSProcCall(“sp_AddValues”)
call.registerInParam(“MultID”,system.db.INT,1)
call.registerInParam(“val2”,system.db.DECIMAL,3)

I receive the following error:
caused by Exception: Error executing system.db.execSProcCall()
caused by GatewayException: The index 3 is out of range.
caused by SQLServerException: The index 3 is out of range.

However the following works
call=system.db.createSProcCall(“sp_AddValues”)
call.registerInParam(“MultID”,system.db.INT,1)
call.registerInParam(“val1”,system.db.DECIMAL,2)

Why do I need to specify the val1 parameter, when I only need val2 and val1 is not required?

2

I’m pretty sure the parameters are 0-indexed, which means the numbering scheme will start at 0. That would explain why param 3 is being reported as ‘out of bounds’. Try running the procedure like this:

call=system.db.createSProcCall(“sp_AddValues”)
call.registerInParam(“MultID”,system.db.INT,0)
call.registerInParam(“val1”,system.db.DECIMAL,2)