Procedure or function 'search_words1' expects parameter '@result', which was not supplied

Question

I am trying to call procedure from java spring boot. I am passing array as a parameter to procedure and from the procedure I want to return the list back to java as a resultset. Please let me know what I doing wrong here. Below is the code:

This is the entity class where I have provided details of the procedure

@Entity
@Table(name="article")
@NamedStoredProcedureQuery(
        name = "search_words", 
        procedureName = "dbo.search_words1", 
        parameters = { 
            @StoredProcedureParameter(
                mode = ParameterMode.IN, 
                name = "list", 
                type = Array.class),
            @StoredProcedureParameter(
                mode = ParameterMode.OUT, 
                name = "result", 
                type = Array.class) 
            }
)
public class Article {
//entity class
}

This is the repository class from where I am trying to call perocedure

//Repository class
@Repository
public interface ArticleRepository extends JpaRepository<Article, Long> {

    public List<Article> findAllByOrderByArticleIdDesc();

    public List<Article> findAllByNavigationOrderByArticleIdDesc(Navigation navigation);

    @Query(nativeQuery = true, value = "exec search_words1")
    public List<Article> find(@Param("list") String[] list);
}

This is the actual procedure created in sql server

    CREATE or ALTER PROCEDURE search_words1 
    @list word_list READONLY,
    @result varchar OUTPUT
AS
BEGIN
SET NOCOUNT ON
    --DECLARE @list as word_list
DECLARE @list_count int;
DECLARE @_count int;
DECLARE @qry varchar(1000);
DECLARE @wording varchar(1000);
set @_count = 0;
--print @list;
--insert into @list values (1,'java');
--insert into @list values (2,'aws');
--insert into @list values (3,'avl');
set @list_count =  (select count(*) from @list)
set @qry = ''
WHILE ( @_count < @list_count )
    BEGIN
    set @_count = @_count + 1;
    set @wording = (select word from @list where id = @_count);
    set @qry = @qry + ' SELECT * FROM article a WHERE LOWER(a.description) like ' + '''%'+@wording+'%'''
    if (@_count <> @list_count)
        set @qry = @qry + ' UNION '
    END;
    EXEC (@qry)
    set @result = @qry
--EXEC dbo.search @list
RETURN @result
END
GO

Getting below error while executing code:

com.microsoft.sqlserver.jdbc.SQLServerException: Procedure or function 'search_words1' expects parameter '@result', which was not supplied.
    at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:232) ~[mssql-jdbc-6.1.0.jre8.jar:na]
    at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1672) ~[mssql-jdbc-6.1.0.jre8.jar:na]
    at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:460) ~[mssql-jdbc-6.1.0.jre8.jar:na]
    at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:405) ~[mssql-jdbc-6.1.0.jre8.jar:na]
    at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7535) ~[mssql-jdbc-6.1.0.jre8.jar:na]
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2438) ~[mssql-jdbc-6.1.0.jre8.jar:na]
    at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:208) ~[mssql-jdbc-6.1.0.jre8.jar:na]
    at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:183) ~[mssql-jdbc-6.1.0.jre8.jar:na]
    at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(SQLServerPreparedStatement.java:317) ~[mssql-jdbc-6.1.0.jre8.jar:na]
    at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52) ~[HikariCP-3.2.0.jar:na]
    at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java) ~[HikariCP-3.2.0.jar:na]
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:60) ~[hibernate-core-5.3.11.Final.jar:5.3.11.Final]
    at org.hibernate.loader.Loader.getResultSet(Loader.java:2265) ~[hibernate-core-5.3.11.Final.jar:5.3.11.Final]
    at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:2028) ~[hibernate-core-5.3.11.Final.jar:5.3.11.Final]
    at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1990) ~[hibernate-core-5.3.11.Final.jar:5.3.11.Final]
    at org.hibernate.loader.Loader.doQuery(Loader.java:949) ~[hibernate-core-5.3.11.Final.jar:5.3.11.Final]
    at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:351) ~[hibernate-core-5.3.11.Final.jar:5.3.11.Final]
    at org.hibernate.loader.Loader.doList(Loader.java:2787) ~[hibernate-core-5.3.11.Final.jar:5.3.11.Final]
    at org.hibernate.loader.Loader.doList(Loader.java:2770) ~[hibernate-core-5.3.11.Final.jar:5.3.11.Final]
    at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2604) ~[hibernate-core-5.3.11.Final.jar:5.3.11.Final]
    at org.hibernate.loader.Loader.list(Loader.java:2599) ~[hibernate-core-5.3.11.Final.jar:5.3.11.Final]
    at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:338) ~[hibernate-core-5.3.11.Final.jar:5.3.11.Final]
    at org.hibernate.internal.SessionImpl.listCustomQuery(SessionImpl.java:2254) ~[hibernate-core-5.3.11.Final.jar:5.3.11.Final]
    at org.hibernate.internal.AbstractSharedSessionContract.list(AbstractSharedSessionContract.java:1069) ~[hibernate-core-5.3.11.Final.jar:5.3.11.Final]
    at org.hibernate.query.internal.NativeQueryImpl.doList(NativeQueryImpl.java:170) ~[hibernate-core-5.3.11.Final.jar:5.3.11.Final]
    at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1506) ~[hibernate-core-5.3.11.Final.jar:5.3.11.Final]
    at org.hibernate.query.Query.getResultList(Query.java:132) ~[hibernate-core-5.3.11.Final.jar:5.3.11.Final]
    at org.springframework.data.jpa.repository.query.JpaQueryExecution$CollectionExecution.doExecute(JpaQueryExecution.java:129) ~[spring-data-jpa-2.1.10.RELEASE.jar:2.1.10.RELEASE]
    at org.springframework.data.jpa.repository.query.JpaQueryExecution.execute(JpaQueryExecution.java:91) ~[spring-data-jpa-2.1.10.RELEASE.jar:2.1.10.RELEASE]
    at org.springframework.data.jpa.repository.query.AbstractJpaQuery.doExecute(AbstractJpaQuery.java:136) ~[spring-data-jpa-2.1.10.RELEASE.jar:2.1.10.RELEASE]
    at org.springframework.data.jpa.repository.query.AbstractJpaQuery.execute(AbstractJpaQuery.java:125) ~[spring-data-jpa-2.1.10.RELEASE.jar:2.1.10.RELEASE]
    at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.doInvoke(RepositoryFactorySupport.java:605) ~[spring-data-commons-2.1.10.RELEASE.jar:2.1.10.RELEASE]
    at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.lambda$invoke$3(RepositoryFactorySupport.java:595) ~[spring-data-commons-2.1.10.RELEASE.jar:2.1.10.RELEASE]
    at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.invoke(RepositoryFactorySupport.java:595) ~[spring-data-commons-2.1.10.RELEASE.jar:2.1.10.RELEASE]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.1.9.RELEASE.jar:5.1.9.RELEASE]
    at org.springframework.data.projection.DefaultMethodInvokingMethodInterceptor.invoke(DefaultMethodInvokingMethodInterceptor.java:59) ~[spring-data-commons-2.1.10.RELEASE.jar:2.1.10.RELEASE]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.1.9.RELEASE.jar:5.1.9.RELEASE]
    at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:295) ~[spring-tx-5.1.9.RELEASE.jar:5.1.9.RELEASE]
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:98) ~[spring-tx-5.1.9.RELEASE.jar:5.1.9.RELEASE]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.1.9.RELEASE.jar:5.1.9.RELEASE]
    at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:139) ~[spring-tx-5.1.9.RELEASE.jar:5.1.9.RELEASE]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.1.9.RELEASE.jar:5.1.9.RELEASE]
    at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodInterceptor.invoke(CrudMethodMetadataPostProcessor.java:144) ~[spring-data-jpa-2.1.10.RELEASE.jar:2.1.10.RELEASE]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.1.9.RELEASE.jar:5.1.9.RELEASE]
    at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$ExposeRepositoryInvocationInterceptor.invoke(CrudMethodMetadataPostProcessor.java:364) ~[spring-data-jpa-2.1.10.RELEASE.jar:2.1.10.RELEASE]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.1.9.RELEASE.jar:5.1.9.RELEASE]
    at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:93) ~[spring-aop-5.1.9.RELEASE.jar:5.1.9.RELEASE]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.1.9.RELEASE.jar:5.1.9.RELEASE]
    at org.springframework.data.repository.core.support.SurroundingTransactionDetectorMethodInterceptor.invoke(SurroundingTransactionDetectorMethodInterceptor.java:61) ~[spring-data-commons-2.1.10.RELEASE.jar:2.1.10.RELEASE]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.1.9.RELEASE.jar:5.1.9.RELEASE]
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:212) ~[spring-aop-5.1.9.RELEASE.jar:5.1.9.RELEASE]
    at com.sun.proxy.$Proxy117.find(Unknown Source) ~[na:na]
    at com.nishant.codecruze.service.ArticleService.getSearchResults(ArticleService.java:64) ~[classes/:na]
    at com.nishant.codecruze.controller.ArticleController.getSearchResults(ArticleController.java:40) ~[classes/:na]
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_181]
    at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source) ~[na:1.8.0_181]

Answer 1

Even if it is an old question, i hope my answer helps someone. You can follow this guide: Calling Stored Procedures JPA

You can reference your prodecures in 3 ways:

1. By its name

In your dao define procedure like this:

@Procedure("PROCEDURE_NAME")
List<String[]/Object/outtype> functionNameCanBeAnything(@Param("paramNameInSP") String inputParam);

call the function in your service. In your case: Stored Procedure might not understand what Article is, your output parameter is defined as varchar so i suggest you that you should use String instead of Article. If there are multiple columns as result you can use String[] then process your output.

@Procedure("search_words1")
List<String> search(@Param("word_list") String inputParam);

2. In Entity

//In your dao 
@Procedure(name = "EntityName.functionName")
    List<String[]> functionName(@Param("inputName") String inputName);
//In your entity define your procedure like this: 
@NamedStoredProcedureQuery(name = "EntityName.functionName",
        procedureName = "ProcedureName", parameters = {
        @StoredProcedureParameter(mode = ParameterMode.IN, name = "inputName", type = String.class),
        @StoredProcedureParameter(mode = ParameterMode.OUT, name = "outParam", type = Integer.class)})

You can also apply this into your case by calling the fınction in your service.

3. With @Query annotation

It might be different for Databases but you can see an example in the guide

Answer 2

Use @Procedure on find method in ArticleRepository. Also ensure that name attribute in @Procedure matches the name attribute value given in @NamedStoredProcedureQuery in entity.