SQL stored procedure call error - The index 1 is out of range

• harbinger910

  SSC Enthusiast

  Points: 199

  More actions

  August 17, 2018 at 8:19 pm

  #371308

  New to SQL and trying to insert 3 fields into an SQL table using a stored procedure call in groovy and getting "index 1 is out of range error" Can someone please help me understand this error. I'm probably not setting up the Stored Procedure call properly or something. Thanks in advance.

  SQL table:

    guidField uniqueidentifier,
    strField varchar(50),
    dateField smalldatetime

  Stored procedure:

    ALTER PROC [dbo].[pCreate_TestRec]
    (
    @guidField uniqueidentifier,
    @strField varchar(50),
    @dateField smalldatetime
    )
    AS
    BEGIN
    INSERT INTO dbo.tblTest(guidField, strField, dateField)
    VALUES (@guidField, @strField, @dateField)
       SELECT SCOPE_IDENTITY()
    END

  Code block:
    long lTimeStamp = d.getTime();
    UUID uuid = UUID.randomUUID()
    sql.call("{call pCreate_TestRec(guidField,strField,dateField)}", [uuid,
    'test', lTimeStamp] );

  Returns the following exception:
  WARNING: Failed to execute: {call pCreate_TestRec(guidField,strField,dateField)} because: The index 1 is out of range.
  Caught: com.microsoft.sqlserver.jdbc.SQLServerException: The index 1 is out of range.
  com.microsoft.sqlserver.jdbc.SQLServerException: The index 1 is out of range.

• laurie-789651

  SSCertifiable

  Points: 7680

  More actions

  August 20, 2018 at 3:45 am

  #2002445

  Hi - Have you still got a problem with this?

  I ran it & it worked OK, as it stands:

  
USE [tempdb];
GO

  DROP TABLE IF EXISTS dbo.tblTest;

  CREATE TABLE dbo.tblTest
(
guidField uniqueidentifier,
strField varchar(50),
dateField smalldatetime
);
GO

  DECLARE @guidField uniqueidentifier = NEWID();
DECLARE @strField varchar(50) = 'TestString';
DECLARE @dateField smalldatetime = CAST(GETDATE() as SmallDateTime);

  EXEC [dbo].[pCreate_TestRec] @guidField, @strField, @dateField;

  SELECT * FROM dbo.tblTest;

  --Stored procedure:

  CREATE PROC [dbo].[pCreate_TestRec]
(
@guidField uniqueidentifier,
@strField varchar(50),
@dateField smalldatetime
)
AS
BEGIN
    INSERT INTO dbo.tblTest(guidField, strField, dateField)
    VALUES (@guidField, @strField, @dateField);
    SELECT SCOPE_IDENTITY();
END

  Note that SCOPE_IDENTITY() returns the last identity value inserted into an identity column in the same scope.  There is no identity column here, so it returns NULL.

  Also, the error message refers to an index but we have no index details given.

  So I'd suggest -
  Try running it in SQL Server Management Studio & see if it works.  If so, the issue is probably in the calling application (code block).

  If it doesn't work, you can post details of any indexes on the table & also some examples of the data being passed to the procedure when the error occurs.

• frederico_fonseca

  SSCoach

  Points: 16154

  More actions

  August 20, 2018 at 6:49 am

  #2002466

  if you change you call code to be

  long lTimeStamp = d.getTime();
  UUID uuid = UUID.randomUUID()
  sql.call("{call pCreate_TestRec(?,?,?)}", [uuid, 'test', lTimeStamp] );

  does it work?

• harbinger910

  SSC Enthusiast

  Points: 199

  More actions

  August 20, 2018 at 7:28 am

  #2002473

  Thanks for your posts. I actually figured it out. The first parameter had to be converted to a string in order for it to be excepted.

  def t = sql.call("{call pCreate_TestRec(?,?,?)}", [UUID.randomUUID().toString(), "Test", new Date().getDate()] );