Mysql Syntax error

Question

I have a syntax error when running the stored procedure, i get it near the +v_RowIndex+' And ('+v_RowIndex+' + '+v_NoOfRows+')';

Can anyone help me?

DELIMITER //; //


CREATE PROCEDURE GetProducts(v_WhereClause  NATIONAL VARCHAR(4000),
v_SortExpression    NATIONAL VARCHAR(128),
v_RowIndex      INT,
v_NoOfRows      INT)
BEGIN 

   DECLARE v_SQL NATIONAL VARCHAR(4000);

   IF (v_WhereClause != '') then

      SET v_WhereClause = CONCAT('WHERE ',char(13),v_WhereClause);
   end if;

   IF (v_SortExpression != '') then

      SET v_SortExpression = CONCAT('ORDER BY ',v_SortExpression);
   end if;

   SET v_SQL = CONCAT('SQLWAYS_EVAL# AS (
SELECT ROW_NUMBER() OVER (',v_SortExpression,
   'SQLWAYS_EVAL#                   
[Id],
[Name],
[Description],
[Unit],
[UnitPrice],
[CreateDate]
FROM 
[Product]   
',v_WhereClause,'SQLWAYS_EVAL#   
Row between ')+v_RowIndex+' And ('+v_RowIndex+' + '+v_NoOfRows+')';

   SET @SWV_Stmt = v_SQL;
   PREPARE SWT_Stmt FROM @SWV_Stmt;
   EXECUTE SWT_Stmt;
   DEALLOCATE PREPARE SWT_Stmt;

   SET v_SQL = CONCAT('SELECT COUNT(Id)
   FROM
   Product ',v_WhereClause);

   SET @SWV_Stmt = v_SQL;
   PREPARE SWT_Stmt FROM @SWV_Stmt;
   EXECUTE SWT_Stmt;
   DEALLOCATE PREPARE SWT_Stmt;

END;
//

user330315 · Accepted Answer · 2011-04-02 21:11:45Z

1

I can see two problems right away:

ROW_NUMBER() OVER (...)

MySQL does not support windowing functions

[Id]

Square brackets are illegal for quoting object names in MySQL (actually in all databases except SQL Server)

answered Apr 2, 2011 at 21:11

user330315

Sign up to request clarification or add additional context in comments.

2 Comments

7effrey Over a year ago

hmm, i have no idea how else to write the script. I can manage to the delete the square brackets, but the windowing functions :(

user330315 Over a year ago

You can do a search on StackOverflow there are several workarounds for MySQL to replace the row_number() function.

Collectives™ on Stack Overflow

Mysql Syntax error

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related