How can I pass a variable to a SQL variable?

[Ricardo1980]

Hello,

This works:

  let sqlTest = `
  SELECT *
  FROM User u
  WHERE state = 'LIVE'
  AND NOT EXISTS(SELECT 1 FROM UserBlock ub1 WHERE ub1.blockedUserId = u.userId AND ub1.blockerUserId = UNHEX(:currentUserId))
  AND NOT EXISTS(SELECT 1 FROM UserBlock ub2 WHERE ub2.blockerUserId = u.userId AND ub2.blockedUserId = UNHEX(:currentUserId))
  LIMIT 4;
`;
let usersDbResponse = await dataApiClient.query(sqlTest, { currentUserId: currentUserId });

This does not work:

  let sqlTest = `
  SET @loggedUserId = :currentUserId;
  SELECT *
  FROM User u
  WHERE state = 'LIVE'
  AND NOT EXISTS(SELECT 1 FROM UserBlock ub1 WHERE ub1.blockedUserId = u.userId AND ub1.blockerUserId = UNHEX(@loggedUserId))
  AND NOT EXISTS(SELECT 1 FROM UserBlock ub2 WHERE ub2.blockerUserId = u.userId AND ub2.blockedUserId = UNHEX(@loggedUserId))
  LIMIT 4;
`;
let usersDbResponse = await dataApiClient.query(sqlTest, { currentUserId: currentUserId });

The error message in CloudWatch (long line):

BadRequestException: Database error code: 1064. Message: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT *  FROM User u  WHERE state = 'LIVE'  AND NOT EXISTS(SELECT 1 FROM Use' at line 2 | 2021-06-09T20:14:13.298Z 4cb8dae9-bc14-4a8d-8e51-85ef14baca21 ERROR Error retrieving users for game: BadRequestException: Database error code: 1064. Message: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT * FROM User u WHERE state = 'LIVE' AND NOT EXISTS(SELECT 1 FROM Use' at line 2
-- | --

How should I pass a variable to a SQL variable (SET)? The reason I need this is because my SQL statement is a bit complex and long and very often I use the AWS query editor and it is better having to change only one thing rather than a lot. Thanks a lot for suggestions!

EDIT:

I just realised this does not work (same error message). So, the issue is not passing the variable, but using SET. Do you know why I cannot use SET? Thanks!

let sqlTest = `
SET @loggedUserId = '3D5F858CF29F4A8289DD9D034F2E943C';
SELECT *
FROM User u
WHERE state = 'LIVE'
AND NOT EXISTS(SELECT 1 FROM UserBlock ub1 WHERE ub1.blockedUserId = u.userId AND ub1.blockerUserId = UNHEX(@loggedUserId))
AND NOT EXISTS(SELECT 1 FROM UserBlock ub2 WHERE ub2.blockerUserId = u.userId AND ub2.blockedUserId = UNHEX(@loggedUserId))
LIMIT 4;
`;
let usersDbResponse = await dataApiClient.query(sqlTest);