mysql存储过程中的变量声明

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.

1)第一个是使用隐式声明,如果我去工作台,就会按预期工作。

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:

2)第二个尝试使用显式声明,但当我尝试运行它到workbech我有一些错误:

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)

错误包括:语法错误:意外'startRowIndex'(标识符)语法错误:意外的page_Count(标识符)

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.

注1:我已经从如何在MySQL中声明变量中找到了帖子?但我无法看到存储过程的版本2的问题。

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."

注意2:如果有人问我为什么不使用存储过程的版本1是因为我的C#安装程序抛出了其他错误消息:“MySql.Data.MySqlClient.MySqlException:必须定义参数'@startRowIndex'。”

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?

更新:此处描述了sqlcommand异常的原因:是否可以在.NET MySqlCommand中使用MySql用户定义变量?

1 个解决方案

#1

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

当我将代码粘贴到Workbench时,它在此行显示错误:

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”.

在存储程序上下文中准备的语句不能引用存储过程或函数参数或局部变量,因为它们在程序结束时超出范围并且将在程序外部稍后执行的语句不可用。作为一种解决方法,请转而使用具有会话范围的用户定义变量;请参见第9.4节“用户定义的变量”。

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

换句话说,您不能将本地(DECLAREd)变量传递给预准备语句;你只能传递会话变量(@ variables。)

#1