JPA Stored Procedure throwing exception: could not extract ResultSet-CodePudding

I am trying to call a basic stored procedure from azure sql which is just returning the number 1, which looks something like this

CREATE PROCEDURE [dbo].[testProc]
@TableName varchar(100)
AS
BEGIN 
SET NOCOUNT ON
SELECT 1
END

I have a spring boot app trying call the stored procedure using the @Query annotation

@Repository
@Transactional
public interface TestDAO extends JpaRepository<TestEntity, Long> {

     @Query(value = "CALL testProc(:TableName)", nativeQuery = true)
     Long invokeTestProc(@Param("TableName") String TableName);

}

however, I get an exception which says "Incorrect syntax near '@P0'" and SQLGrammarException: could not extract ResultSet.

I am not sure how to fix this, I tried using the @Procedure with @NamedStoredProcedureQueries annotations and it threw another exception saying "Cannot mix positional parameter with named parameter registrations;"

CodePudding user response：

According to the documentation for Azure Sql, you have to call the stored procedure with the following

EXECUTE HumanResources.uspGetEmployeesTest2 N'Ackerman', N'Pilar';
-- Or
EXEC HumanResources.uspGetEmployeesTest2 @LastName = N'Ackerman', @FirstName = N'Pilar';

So in your case this would be translated as

 @Query(value = "EXECUTE testProc :TableName", nativeQuery = true)
 Long invokeTestProc(@Param("TableName") String TableName);

 @Query(value = "EXEC testProc @TableName = :TableName", nativeQuery = true)
 Long invokeTestProc(@Param("TableName") String TableName);

So considering that you use native queries, the

exception which says "Incorrect syntax near '@P0'" and SQLGrammarException: could not extract ResultSet.

reffers to the wrong use of sql grammar where you don't use execute or exec and you also pass the parameter in a way not expected for Azure SQL.