Getting an Syntax error try limit offset on SQL Server about querybuilder HOT 8 CLOSED

Hi thanks for your feedback, yes this already mentioned in #13, it will get fixed this weekend hopefully :)

from querybuilder.

Closing it, since fixed on the https://www.nuget.org/packages/SqlKata/1.0.0-beta-354 package

from querybuilder.

Hi, I have testet the Solution and it works fine, but why do you not use OFFSET FETCH NEXT, like this:
"SELECT * FROM [dbo].[Products] ORDER BY ProductID ASC OFFSET @offset ROWS FETCH NEXT @next ROW ONLY"

Maybe you get a Performance issue with your Solution.

tanks

sascha

from querybuilder.

@sbur74, because this will not work on the old version of SqlServer <2012, maybe we can add a new compiler to target the modern SqlServer engines

from querybuilder.

@ahmad-moussawi:
Hi, thanks a lot for you explanation, is it possible to write an extension for is or is it necessary to write a completely new compiler?

tanks and regards

sascha

from querybuilder.

@sbur74 yes you should write a new compiler for this, one way to do this, is to extend the current SqlServerCompiler class and override the following methods OnBeforeSelect, CompileColumns, CompileLimit and CompileOffset, check https://github.com/sqlkata/querybuilder/blob/master/QueryBuilder/Compilers/SqlServerCompiler.cs#L24-L135, we would be happy if you contribute back to this project :)

from querybuilder.

@ahmad-moussawi: thanks a lot for you invention.
I'll try my best to make a running complier.

from querybuilder.

@ahmad-moussawi : i have to place this "OFFSET {0} ROWS FETCH NEXT {1} ROWS ONLY" clause after ORDER BY

I thought i'll could do this with a RawCondition:
query.Clauses.Insert(query.Clauses.Count, new RawCondition
{
Component = "",
Engine = EngineCode,
Bindings = new object[] { limitOffset.Offset, limitOffset.Limit },
Expression = "OFFSET {0} ROWS FETCH NEXT {1} ROWS ONLY"
});

but this has no impact on the SqlResult.
any idea why this could be?

from querybuilder.