How to call a MSSQL stored procedure from java

Question

I am trying to call a stored procedure from java and I'm having some issues getting the call to go through correctly. I've looked up numerous examples, but they don't match the syntax of the stored procedure I'm trying to call.

    STORED PROCEDURE CODE
    ------------------------
    GO
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ALTER PROCEDURE [dbo].[getMgr]
    @UserID as varchar(5),
    @ModeType as char(1)
    AS
    BEGIN
    SELECT usermgrid from AllEmployees WHERE (ModeType = @ModeType AND UserID = 
    @UserID)
    END

JAVA CODE
----------------------------

    connection = getConnection();
    cs = connection.prepareCall("{call lvhsp_GetManager(?, ?) }");
    cs.setString(0, someUserID);
    cs.setString(1, "0");
    ResultSet resultSet = cs.executeQuery();

I've tried calling the stored procedure a number of different ways but I continue to get errors.

When I call it like this, I get a:

com.microsoft.sqlserver.jdbc.SQLServerException: The index 0 is out of range.

Which I assume is because that is not the correct way to pass a parameter.

I've also tried calling it with the parameter names (UserID, ModeType) with and without the @, but that doesn't work.

What am I missing?

Thanks, I tried start at 1 as well - com.microsoft.sqlserver.jdbc.SQLServerException: The index 0 is out of range. — John Cressman, Feb 05 '19 at 14:47
Possible duplicate of [How do I execute a MS SQL Server stored procedure in java/jsp, returning table data?](https://stackoverflow.com/questions/6113674/how-do-i-execute-a-ms-sql-server-stored-procedure-in-java-jsp-returning-table-d) — nullPointer, Feb 05 '19 at 14:54

score 2 · Answer 1 · answered Feb 05 '19 at 14:25

2

Try with the below instead :

cs.setString(1, someUserID);
cs.setString(2, "0");

answered Feb 05 '19 at 14:25

nullPointer

4,419
1
15
27

I actually tried that before. I get the same error - com.microsoft.sqlserver.jdbc.SQLServerException: The index 0 is out of range. – John Cressman Feb 05 '19 at 14:45
Then check some recommendation here : https://stackoverflow.com/questions/6113674/how-do-i-execute-a-ms-sql-server-stored-procedure-in-java-jsp-returning-table-d – nullPointer Feb 05 '19 at 14:55
I'd add: Make sure that you actually compiled and deployed the changed code. Analyze in which line the exception was thrown - if it's in the first `cs.setString` line, then you might have a mismatch between deployed code and source. – Olaf Kock Feb 06 '19 at 12:13

How to call a MSSQL stored procedure from java

1 Answers1