I have the need to write a query like below in order to limit the number of rows returned to 1000 at a time. I need to write similar to the following for performance reasons. The query plan goes awry for the left join portion in my specific query unless I tell it to limit TABLE2's FKID as well.
SELECT t1.ID, ...
FROM Table1 t1
LEFT JOIN TABLE2 t2 ON t1.ID = t2.FKID AND t2.FKID > ? AND t2.FKID <= ? + 1000
WHERE t1.ID > ? AND t1.ID <= ? + 1000
However, I receive this error message when clicking the "Execute SQL" button.
com.microsoft.sqlserver.jdbc.SQLServerException: The value is not set for the parameter number 2
Does anyone have any suggestions to resolve? The only option I can think of is to create a stored proc that accepts a single param to resolve.
Thanks!
Brian