MS SQL Exception: Incorrect syntax near '@P0'

Question

I'm querying a DB using MS SQL and for some reason I get the following error: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near '@P0' even though this 'P0' isn't anywhere in my syntax...

I've read that someone has had a same issue but they were using a stored proc, something which I am not using so I don't see how his solution will work for me. (His solution being asomething about adding braces {} around the procedure call.

Anyways, below I have pasted the relevant code. Really hope someone can help me with this, getting quite frustrated.

PreparedStatement stmt = null;
Connection conn = null;    

String sqlQuery = "SELECT TOP ? \n"+
                              "z.bankAccountNo, \n"+
                              "z.statementNo, \n"+
                              "z.transactionDate, \n"+
                              "z.description, \n"+
                              "z.amount, \n"+
                              "z.guid \n"+
                              "FROM \n"+
                              "( \n"+
                              "select  \n"+
                              "ROW_NUMBER() OVER (ORDER BY x.transactionDate, x.statementNo) AS RowNumber, \n"+
                              "x.transactionDate, \n"+
                              "x.statementNo, \n"+
                              "x.description, \n"+
                              "x.amount, \n"+
                              "x.bankAccountNo, \n"+
                              "x.guid \n"+
                              "FROM \n"+
                              "( \n"+
                              "SELECT  \n"+
                              "a.bankAccountNo,  \n"+
                              "a.statementNo,  \n"+
                              "a.transactionDate, \n"+
                              "a.description,  \n"+
                              "a.amount,  \n"+
                              "a.guid  \n"+
                              "FROM BankTransactions as a  \n"+
                              "LEFT OUTER JOIN BankTransactionCategories as b  \n"+
                              "ON a.category = b.categoryCode  \n"+
                              "WHERE b.categoryCode is null \n"+
                              ") as x \n"+
                              ") as z \n"+
                              "WHERE (z.RowNumber >= ?)";

stmt = conn.prepareStatement(sqlQuery);
stmt.setInt(1, RowCountToDisplay);
stmt.setInt(2, StartIndex);
ResultSet rs = null;
try{
    rs = stmt.executeQuery();
} catch (Exception Error){
    System.out.println("Error: "+Error);
}

Can you provide actual sql causing the error? It seems like you do not properly provide `RowCountToDisplay` — VMAtm, Aug 12 '11 at 10:31

score 131 · Accepted Answer · answered Aug 12 '11 at 10:30

131

SQL Server requires you to place parenthesis around the argument to top if you pass in a variable:

SELECT TOP (?)

answered Aug 12 '11 at 10:30

Andomar

232,371
49
380
404

1

Thank you! this was the problem... Been struggling with this for hours. Always a little thing isn't it... Thanks again. – Aug 12 '11 at 10:44
4

This error can also appear with `INSERT` queries on syntax error binding. The number after `@P` characters can indicate which parameter produces the error (`@P3` for the third parameter, ...). – Fractaliste Jul 25 '14 at 12:37
1

Likely time spent on this problem without SO: 3-8 hours Time spent on this problem with SO: 55 seconds. Thank you! – jumps4fun Oct 28 '15 at 11:53
Right you are! Thanks. – John Lockwood Nov 04 '15 at 22:55
Although this is the correct, I found the solution to be @Tomasz Godziński answer – Mark W Jul 10 '17 at 12:23
But how we place parenthesis around TOP, if we are using Pageable request – user3029929 Sep 26 '17 at 10:42
Thanks. I knew this long ago but forgot. – Mark.ewd Feb 23 '19 at 21:44
Glad I found this. Wonder where it might be documented and how one would know if it wasn't for SO? – trebor Feb 12 '23 at 23:02
how to do dynamic query, like i am creating query using StringBuilder and needs to pass that query to repository to fetch data. how can achieve this type of thing? – Irfan Nasim Mar 02 '23 at 05:38

score 22 · Answer 2 · answered Sep 02 '16 at 14:02

22

In our application we have extended a depraceted SQLServerDialect. After change to SQLServer2008Dialect the problem disappeared.

answered Sep 02 '16 at 14:02

Tomasz Godziński

483
4
14

1

I experienced the same after moving to Spring Boot 1.4 hence new Hibernate version – Jose Jurado Sep 30 '16 at 07:54
2

I got this when using `PageRequest`. I'm guessing, as per the answer from @Andomar, that it is implemented using a "top" without parentheses. As you say, updating from `SQLServerDialect` (in my case to 2012) seems to fix the issue. Apparently, this was a change in SQL Server 2005: http://www.dotnetfunda.com/articles/show/59/difference-between-top-clause-in-sql-2000-and-sql-2005 – Steve Oct 31 '16 at 11:55

score 12 · Answer 3 · answered Dec 20 '16 at 23:21

Upgraded hibernate to version 5.x and came across this issue. Had to update "hibernate.dialect" configuration from org.hibernate.dialect.SQLServerDialect to org.hibernate.dialect.SQLServer2012Dialect. Fixed the issue!

Hibernate Doc Reference: https://docs.jboss.org/hibernate/orm/3.6/reference/en-US/html/session-configuration.html#configuration-programmatic

Hibernate Jira issue: https://hibernate.atlassian.net/browse/HHH-10032

score 1 · Answer 4 · answered Feb 02 '15 at 05:40

It can also be caused by a syntax error in your SQL as was the case for me

select * from drivel d where exists (select * from drivel where d.id = drivel.id and drivel.start_date < '2015-02-05' AND '2015-02-05' < drivel.end_date) OR exists (select * from drivel where d.id = drivel.id and drivel.start_date < '2015-02-05' AND '2015-02-05' < drivel.end_date) OR exists (select * from drivel where d.id = drivel.id and '2015-02-05' < drivel.start_date and drivel.end_date < '2015-02-05'

gave the message

com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near '@P5'

the problem was actually the balancing ')' missing at the end, namely, correct version is

select * from drivel d where exists (select * from drivel where d.id = drivel.id and drivel.start_date < '2015-02-05' AND '2015-02-05' < drivel.end_date) OR exists (select * from drivel where d.id = drivel.id and drivel.start_date < '2015-02-05' AND '2015-02-05' < drivel.end_date) OR exists (select * from drivel where d.id = drivel.id and '2015-02-05' < drivel.start_date and drivel.end_date < '2015-02-05')

score 1 · Answer 5 · answered Mar 21 '18 at 20:42

If you are using custom data source, adding property :

spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.SQLServer2012Dialect

in application.properties will not work.

You have to add the property as properties map in your data source bean:

  @Bean
public LocalContainerEntityManagerFactoryBean sqlServerEntityManagerFactory() {
    HashMap<String, String> properties = new HashMap<>();
    properties.put("hibernate.dialect", "org.hibernate.dialect.SQLServer2012Dialect");

    LocalContainerEntityManagerFactoryBean factoryBean = new LocalContainerEntityManagerFactoryBean();
    factoryBean.setDataSource(sqlServerDataSource());
    factoryBean.setJpaVendorAdapter(new HibernateJpaVendorAdapter());
    factoryBean.setJpaPropertyMap(properties);
    return factoryBean;
}

Meysam · Answer 6 · 2018-06-07T14:29:42.390

1

If you are using Hibernate within a spring-boot app, you may set hibernate.dialect with following configuration property:

spring.jpa.database-platform=org.hibernate.dialect.SQLServer2008Dialect

edited Jun 07 '18 at 14:29

answered May 27 '18 at 05:43

Meysam

587
9
15

Works well with Spring Roo. Simply provide `property add --key spring.jpa.properties.hibernate.dialect --value org.hibernate.dialect.SQLServer2012Dialect` at the end of a roo script. – J E Carter II Oct 31 '19 at 15:58

score -2 · Answer 7 · edited Jun 20 '20 at 08:48

Call the Procedure in the below way

@Override
public List<Rep_Holdings> getHoldingsReport(
        int pid
)
{
    List<Rep_Holdings> holdings = null;

    Session sess = sFac.getCurrentSession();
    if (sess != null && pid > 0)
    {
        @SuppressWarnings(
            "rawtypes"
        )
        Query query = sess.createSQLQuery(
                "{CALL GetHoldingsforPF(:pid)}").addEntity(Rep_Holdings.class);
        query.setParameter("pid", pid);

        @SuppressWarnings(
            "rawtypes"
        )
        List result = query.getResultList();
        if (result != null)
        {
            if (result.size() > 0)
            {
                holdings = new ArrayList<Rep_Holdings>();
                for (int i = 0; i < result.size(); i++)
                {
                    Rep_Holdings holding = (Rep_Holdings) result.get(i);
                    holdings.add(holding);
                }
            }
        }
    }

    return holdings;
}

The same procedure in SQL Server

ALTER PROCEDURE [dbo].[GetHoldingsforPF]
    @pid int
AS
BEGIN
SET NOCOUNT ON;
        -- Insert statements for procedure here

        declare @totalPFInv decimal(15,2);

    set @totalPFInv =  ( select  sum(totalInvestment) from Holdings where pid = @pid );

    Select hid,
           pid,
           scCode,
           numUnits,
           avgPPU,
           adjPPU,
           totalInvestment,
           cast ( (totalInvestment/@totalPFInv)*100 as decimal(10,1)) as perPF,
           totalDiv,
           cast ( (totalDiv/totalInvestment)*100 as decimal(10,1)) as divY
           from Holdings
           where pid = @pid
END

You seem to be answering a different question than is asked here. The OP isn't using a stored procedure, so your answer doesn't make sense. — Mark Rotteveel, Jun 20 '20 at 08:57

MS SQL Exception: Incorrect syntax near '@P0'

7 Answers7

Linked

Related