Incorrect syntax near the keyword 'with' (SQL)

Question

Hi Guys,

I have a little bit of an odd issue.

When I run this SQL:

with ID_Table as ( 
    select row_number() over (order By SS_ID) As row_id, 
             ss_id 
      from slide_show ) 
     select t0.* 
       from ID_Table as t1 
inner Join slide_show as t0 on t1.ss_id = t0.ss_id 
    where t1.row_id between 0 and 1 
 order by t1.row_id asc;

in SQL Express, it runs and returns the first row as it should (similar to the Limit 0,1 in MySQL). However, when I run this in delphi via the TADOQuery object, I receive the error in the title. I am guessing the ADO object is doing something along the way but I don't understand what.

Note: This is the exact SQL I am passing into the ADO object.

Does anyone have any ideas?

Answer 1

Try:

SELECT t0.*
FROM (SELECT row_number() over(ORDER BY SS_ID) AS row_id, ss_id FROM slide_show) AS t1
INNER JOIN slide_show AS t0
ON t1.ss_id = t0.ss_id
WHERE t1.row_id BETWEEN 0 AND 1
ORDER BY t1.row_id ASC;

Answer 2

The WITH keyword needs to be preceded by a semicolon when there are other statements before it in a batch (technically speaking, the preceding statement has to be terminated with ";", but just putting it before the WITH is a bit easier to maintain).

My guess is that ADO sets a connection variable or something similar, so the WITH is no longer first in the batch. Change it to ";WITH" and see if that works.

Answer 3

Which OLE DB provider are you specifying in your connection string? To be able to use the WITH (CTE) syntax you need to use the SQL Native Client provider e.g.

Provider=SQLNCLI10.1

rather than say the SQL Server OLE DB Provider e.g.

Provider=SQLOLEDB.1