CODEWITHSUNDEEP
mysqllimitconcatenation
Hary
Hary

Reputation: 5818

syntax error with concat and limit in mysql

I am getting syntax error on runnning this. Is it possible to use variables with limit without using concat function?

CREATE PROCEDURE SP(_start INT,_end INT)
BEGIN
     DECLARE _qry VARCHAR(500) DEFAULT CONCAT('select * from tbl limit ',_start,_end);
     PREPARE stmt FROM _qry;
 EXECUTE stmt;
 DEALLOCATE PREPARE stmt;
END

Error is

Error Code: 1064
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 '_qry;
 EXECUTE stmt;

Upvotes: 0

Views: 799

Answers (3)

Nicola Cossu
Nicola Cossu

Reputation: 56397

delimiter //
drop procedure if exists SP //
create procedure SP(_start int,_end int)
begin
     declare _qry varchar(500);
     set @_qry = 'select * from tbl limit ?, ?';
     set @start = _start;
     set @end = _end;
     prepare stmt from @qry;    
     execute stmt using @start, @end;
     deallocate prepare stmt;
end; //
delimiter ;

call SP(1,2);

Upvotes: 1

John Woo
John Woo

Reputation: 263823

I think you have to change the DEFAULT DELIMITER first and adding PARAMETER DIRECTION before creating the STORED PROCEDURE.

There are good reasons to use prepared statements:
1. ) Save on query parsing
2.) Save on data conversion and copying
3.) Avoid SQL Injection
4.) Save memory on handling blobs

There are also drawbacks and chewats of using prepared statements:

1.) Query cache does not work
2.) Extra server round trip required if statement used only once
3.) Not all statements can be prepared. So you can’t use prepared API 
    exclusively you’ll need to fall back to normal API for some statements
4.) Newer and sometimes buggy code. I had a lot of problems with PHP 
    prepared statements. It is getting better but still it is less mature 
    than standard API
5.) You can’t use placeholders in place of all identifiers. For example you 
    can’t use them for table name. In certain version it even does not work for 
    LIMIT boundaries
6.) Inconvenient list handling. Unlike in for example PEAR emulated prepard 
    statements there is no nice way to pass list of values to IN
7.) Harder tracing. Logs were now fixed to include full statement text not 
    only “Execute” but in SHOW INNODB STATUS you would still see statements 
    without actual values – quite inonvenient for analyses.

try this one:

UPDATE 1

DELIMITER $$

CREATE PROCEDURE SP(IN _start INT,IN _end INT)

BEGIN

     SET @iQuery = CONCAT('select * from tbl limit ', _start, ',', _end);

     PREPARE stmt FROM @iQuery;
     EXECUTE stmt;
     DEALLOCATE PREPARE stmt;

END $$

DELIMITER ;

MySQL Syntax for Prepared Statements

Upvotes: 1

xdazz
xdazz

Reputation: 160893

You missed , before the offset.

CREATE PROCEDURE SP(_start INT,_end INT)
BEGIN
     DECLARE _qry VARCHAR(500) DEFAULT CONCAT('select * from tbl limit ', _start, ',', _end);
     PREPARE stmt FROM _qry;
 EXECUTE stmt;
 DEALLOCATE PREPARE stmt;
END

Upvotes: 2

Related Questions