0

I am having a query that uses a declare statement before executing the actual query. As I am using MSSQL server. So, my query is like the the below:

DECLARE @IdParam NVARCHAR(50) = (
SELECT TOP 1 PartyID
FROM PartyInfo
WHERE PartyName = ?1
ORDER BY IsPrimary DESC);

SELECT *
FROM TutorialInfo
WHERE UserId = @IdParam

And my repository has the following method that call native NamedQuery

@Query(nativeQuery = true)
List<UserConvictedCases> getUserConvictedCasesByQid(String qid, Pageable pageable);

My issue that when I execute that method, I am getting the below exception because the pageable is being inserted in the wrong place in the query.

failed: org.springframework.dao.InvalidDataAccessResourceUsageException: JDBC exception executing SQL [DECLARE @IdParam NVARCHAR(50) = (
SELECT TOP 1 PartyID
FROM PartyInfo
WHERE PartyName = ?1
ORDER BY IsPrimary DESC) order by @@version offset 0 rows fetch first ? rows only;

I have a workaround of course for this, that I will use the declared variable in each place I need it. But my question is about is there anyway that I can get this to work and put the pageable parameter in the right place in the query?

Thank you in advance

Mohammed Salah
  • 81
  • 9
  • 1
    There's something called JOINs that have been recently implemented in SQL Server. It allows you to read data from two tables at the same time and can simplify stuff to avoid variables, TOP 1 etc. It's pretty cool, you should check it out! – siggemannen Feb 23 '23 at 17:04
  • 1
    @siggemannen I can't stop laughing, they should make a new award on stackoverflow for the most funniest person, and of course give it to you. BTW I made my example like this, just for showing case, but my actual query is way more complex than this -of course- – Mohammed Salah Feb 25 '23 at 12:47
  • Alright, so assuming you couldn't change your query to avoid variables and do some JOINs :D. Does this help your question? https://stackoverflow.com/a/41283553/13061224 Ie. you have to change your last query to SELECT * FROM TutorialInfo WHERE UserId = @IdParam ORDER BY #pageable – siggemannen Feb 25 '23 at 13:05
  • @siggemannen thanks a lot, this actually did the trick. Thanks again for your help. – Mohammed Salah Feb 26 '23 at 09:50

0 Answers0