0

I have a function in MS SQL like:

CREATE FUNCTION FOO (@param1 int,@param2 int)
RETURNS table
AS
    RETURN (SELECT a, b, c
            FROM MyTable
            WHERE p = @param1
              AND (@param2 IS NULL
                OR x = @param2));

And a Entity Class like:

    @Entity
    @SqlResultSetMappings=(value={
       @SqlResultSetMapping(
          name="fooMapping"
          classes=@ConstructorResult(
             targetClass=Foo.class,
             columns= {
                @ColumnResult(name="a", type=Long.class),
                @ColumnResult(name="b", type=Long.class),
                @ColumnResult(name="c", type=Long.class)}
          )
@NamedNativeQuery(
  name="getFoos",
  query="CALL FOO(:param1, :param2) ORDER BY c",
  resultSetMappings="fooMapping",
  callable=true)
public class Foo {
   ...
}

When I try to call the function with:

TypedQuery<Foo> query = em.createNamedQuery("getFoos", Foo.class);
query.setParameter("param1", param1);
query.setParameter("param2", null);
List<Foo> result = query.getResultList();

Then I get SQLServerException:

Incorrect syntax near '@P0'.

What do I do wrong?

Thank you in advance for your help.

Moki
  • 21
  • 3
  • You're missing a data type for `@param2`. – Thom A Feb 21 '19 at 12:34
  • @param2 is set to NULL. I get the same Exception when I set @param2=123L. – Moki Feb 21 '19 at 12:48
  • No, it doesn't have a data type: `CREATE FUNCTION FOO (@param1 int,@param2)` Between `@Param2` and the right parenthesis(`)`) there needs to be a datatype. – Thom A Feb 21 '19 at 12:49
  • You are right. I forgot to set '@param2' to int in the example code above. But '@param2' has type int – Moki Feb 21 '19 at 12:52
  • If you get some exception then you can easily post the stack trace, and the actual SQL invoked (from the JPA providers log). aka debugging –  Feb 21 '19 at 16:40
  • 1
    Hibernate can't call function? https://vladmihalcea.com/how-to-call-sql-server-stored-procedures-and-functions-from-hibernate/ – Alex Feb 21 '19 at 20:35

0 Answers0