I'm trying the following mysql script, but it says NumOfRecords and Offsetpage not defined.
SET @NumOfRecords = 10;
SET @Offsetpage = 0;
SET @DocumentStatus = 'PENDING';
SELECT * FROM documents WHERE DOCUMENT_VERIFICATION_STATUS = @DocumentStatus ORDER BY DOCUMENT_UPLOADED_DATE DESC LIMIT @NumOfRecords OFFSET @Offsetpage
Any help ?
User variables in the LIMIT clause is not supported.
https://dev.mysql.com/doc/refman/8.0/en/user-variables.html says:
User variables may be used in most contexts where expressions are permitted. This does not currently include contexts that explicitly require a literal value, such as in the
LIMITclause of aSELECTstatement, or theIGNORE N LINESclause of aLOAD DATAstatement.
In other words, you must use literal integers, or else you can use a prepared statement:
PREPARE stmt FROM 'SELECT * FROM documents WHERE DOCUMENT_VERIFICATION_STATUS = ? ORDER BY DOCUMENT_UPLOADED_DATE DESC LIMIT ? OFFSET ?';
EXECUTE stmt USING @DocumentStatus, @NumOfRecords, @Offsetpage;
You do not have to use a stored procedure for this, you can do prepared statements directly in any database session.
You can run prepared statements directly in most client APIs, without using the PREPARE and EXECUTE syntax. You don't have to define user variables either.
For example, in PHP:
<?php
$stmt = $pdo->prepare('SELECT * FROM documents WHERE DOCUMENT_VERIFICATION_STATUS = ? ORDER BY DOCUMENT_UPLOADED_DATE DESC LIMIT ? OFFSET ?');
$stmt->execute( [ 'PENDING', 10, 0 ] );
