Common Table Expressions

Hi,

I've been using HeidiSQL for a while now and absolutely love it. I've come across a problem with Common Table Expressions and am wondering if it is a bug?

If you have a look at the tutorial at SQL Shack titled sql-server-common-table-expressions-cte (sorry I've just joined and can't post URL's) the first query works fine, but the second one fails. Here is the query and the error is below.

Declare @RowNo int =1;
;with ROWCTE as  
   (  
      SELECT @RowNo as ROWNO    
        UNION ALL  
      SELECT  ROWNO+1  
  FROM  ROWCTE  
  WHERE RowNo < 10
    )  

SELECT * FROM ROWCTE ;

/ SQL Error (102): Must declare the scalar variable "@RowNo" Incorrect syntax near ')'. / / Affected rows: 0 Found rows: 0 Warnings: 0 Duration for 1 of 2 queries: 0.000 sec. /

I'm connected to a MS SQL server to run this query. The reason I ask if this is a bug in HeidiSQL is because the exact same query runs fine in SSMS.

I've never used CTE's before, so I don't know if it is a problem with the query or something that HeidiSQL just can't do yet.

Any help would be greatly appreciated.

Many thanks

David

I get the same error in HeidiSQL, and I suppose if you declare variables they are quite volatile.

If you activate "Send batch in one go", it works here:

Hi ansgar. That works perfectly. Thanks.