Variables declaration in mysql stored procedure

Question

I have two version of the same store procedure:

1)The first one is using implicit declaration and is working as expected if I go to workbench.

DROP procedure IF EXISTS `Elmah_GetErrorXml`;
DELIMITER $$

CREATE PROCEDURE `Elmah_GetErrorXml` (IN  `pApplication` NVARCHAR(60),  IN  `pPageIndex`     INT,   IN  `pPageSize`  INT,   OUT `pTotalCount` INT)

BEGIN   
  SELECT COUNT(*) INTO `pTotalCount` FROM `Elmah_Error` WHERE `Application`= pApplication;

  SET @startRowIndex = pPageIndex * (pPageSize + 1);
  SET @page_Count = pPageSize;
  PREPARE STMT FROM 'SELECT * FROM `elmah_error` WHERE `Application`=Application ORDER BY `TimeUtc` DESC, `Sequence` DESC LIMIT ?,?';
  EXECUTE STMT USING @startRowIndex, @page_Count;
END$$
DELIMITER $$

2) the second one is trying to use explicit declaration but when I try run it in to workbech i got some errors:

DROP procedure IF EXISTS `Elmah_GetErrorXml`;
DELIMITER $$
CREATE PROCEDURE `Elmah_GetErrorXml` (IN  `pApplication` NVARCHAR(60),  IN  `pPageIndex`     INT,   IN  `pPageSize`  INT,   OUT `pTotalCount` INT)
BEGIN
    DECLARE startRowIndex INT DEFAULT 0;
    DECLARE page_Count INT DEFAULT 0;
    SELECT COUNT(*) INTO `pTotalCount` FROM `Elmah_Error` WHERE `Application`= pApplication;
    SET startRowIndex = pPageIndex * (pPageSize + 1);
    SET page_Count = pPageSize;
    PREPARE STMT FROM 'SELECT * FROM `elmah_error` WHERE   `Application`=Application ORDER BY `TimeUtc` DESC, `Sequence` DESC LIMIT ?,?';
    EXECUTE STMT USING startRowIndex, page_Count;
END$$
DELIMITER $$

The errors are: Syntax error: unexpected 'startRowIndex' (identifier) Syntax error: unexpected page_Count (identifier)

I would like to know that should be the correct syntaxis in case to use explicit declaration. Any suggestion?

Note 1: I have readed the post from How to declare a variable in MySQL? but i can't see the problem with the version 2 of the stored procedure.

Note 2: if someone ask why I am not using the version 1 of the stored procedure is because my C# installer is throwing other error message: "MySql.Data.MySqlClient.MySqlException : Parameter '@startRowIndex' must be defined."

UPDATE: the reason of the exception from sqlcommand is described here: Is it possible to use a MySql User Defined Variable in a .NET MySqlCommand?

score 1 · Accepted Answer · answered Mar 31 '16 at 01:33

1

When I pasted your code into Workbench, it showed the error on this line:

EXECUTE STMT USING startRowIndex, page_Count;

According to the documentation:

A statement prepared in stored program context cannot refer to stored procedure or function parameters or local variables because they go out of scope when the program ends and would be unavailable were the statement to be executed later outside the program. As a workaround, refer instead to user-defined variables, which also have session scope; see Section 9.4, “User-Defined Variables”.

So in other words, you can't pass local (DECLAREd) variables to a prepared statement; you can only pass session variables (@ variables.)

answered Mar 31 '16 at 01:33

Darwin von Corax

5,201
3
17
28

See [this SO article](http://stackoverflow.com/questions/1471570/dynamic-mysql-with-local-variables) for more information. – Tim Biegeleisen Mar 31 '16 at 01:34
I mark this answer as correct because help me to understand that I have to follow with user defined variables. Now I have to understand why mysql sqlcommand (.net connector) is throwing this error:"@startRowIndex' must be defined. " Thank you both :) – Rolando Mar 31 '16 at 01:56

Variables declaration in mysql stored procedure

1 Answers1