MS SQL Limit option not working

0
votes

I have built a query with a few condition statements added on. They all work fine, but when I type to had a limit option, I only get errors.

So I am using go 1.10 with Gorm and the Gin framework. This is my current working code,

qb := myDB.Table("table").Select("xx, xxx, xxx, xxx")

rows, err := qb.Rows()

if err != nil {
    fmt.Println(err)
}

defer myDB.Close()

return rows

This is all working, but when I add a limit anywhere, e.g. before the table or after, I tried both but did not really think it made a difference? For example, 

qb := myDB.Table("table").Limit(3).Select("xx, xxx, xxx, xxx")

Now I know MS SQL does not use limit but uses TOP within the select statement (forgive me if that is not the only use case, still not used MS SQL much).

The error I get back is, 

mssql: Invalid usage of the option NEXT in the FETCH statement.

Now I have found the following on Gorm's GitHub, https://github.com/jinzhu/gorm/issues/1205

They have a work around but that will not work for me. The guy has also posted an updated function to correct the issue. However, I am not sure how I could update the code in a third party lib. Also this was posted in 2016, so not sure if that code has already been added to the Gorm code base.

3
sql-servergogo-gorm

3 Answers

1
votes

You need an Order By to use OFFSET + FETCH in the generated SQL sent to SQL Server, which would be added by your ORM

...the following conditions must be met:

...

The ORDER BY clause contains a column or combination of columns that are guaranteed to be unique.

How GORM does it, I don't know

0
votes

I am posting this as an answer, i am guesting its more a work around and please let me know if this not recommend for any reason.

My working solution,

qb := myDB.Table("table").Select("TOP(?) xx, xxx, xxx, xxx", 3)

Thanks

0
votes

I'm facing similar issue. Got it working by adding .Order like this:

qb := db.Table("tableName").Order("columnName").Limit(2).Select("col1, col2")