Error al crear Stored Procedure

[Baldur]

Hola!
Estoy intentado crear un Procedimiento almacenado desde el MySQL Workbench pero este me alerta de un error en el primer SET sqlStatement (también he tenido que cambiar nvarchar(max) por nvarchar(1000)):

Código MySQL:

DELIMITER $$
 
CREATE PROCEDURE `galiciaparty`.`spGetAllEventos` (IN startIndex int,IN pageSize int,IN sortBy nvarchar(30),OUT totalEventos int)
BEGIN
DECLARE sqlStatement nvarchar(1000);
DECLARE upperBound int;
    
    IF startIndex  < 1 THEN SET startIndex = 1;
    IF pageSize < 1 THEN SET pageSize = 1;
    SET upperBound = startIndex + pageSize;
    Select totalEventos=Count(*) From gp_eventos_prin
    SET sqlStatement ='SELECT E.id_evento, E.titulo, E.fecha_evento, E.lugar
                       FROM (SELECT  ROW_NUMBER() OVER(ORDER BY ' + sortBy + ') AS rowNumber, *
                             FROM    Employee
                            ) AS E
                       WHERE  rowNumber >= ' + CONVERT(varchar(9), startIndex) + ' AND rowNumber <  ' + CONVERT(varchar(9), upperBound)
  exec (sqlStatement)
  
END$$
END$$
 

Este store procedure lo voy a ejecutar desde C#.NET, por eso le tengo que pasar parámetros. Lo quiero utilizar para paginar los resultados.
Ah, y este procedure lo estoy "portando" de SQL server, donde funciona perfectamente a MySQL...pero no logro que funcione...
Os dejo aquí si quereis el original de SQL Server:

Código SQL:

CREATE PROCEDURE spGetAllEmployee
    (
    @startIndex     INT,
    @pageSize       INT,
    @sortBy     nvarchar(30),
    @totalEmployees INT OUTPUT      
    )
AS
    SET NOCOUNT ON 
 DECLARE
    @sqlStatement nvarchar(MAX),    
    @upperBound INT
  IF @startIndex  < 1 SET @startIndex = 1
  IF @pageSize < 1 SET @pageSize = 1
  SET @upperBound = @startIndex + @pageSize
 SELECT @totalEmployees=COUNT(*) FROM Employee
  SET @sqlStatement = ' SELECT E.EmployeeID, E.EmployeeCode, E.Name, E.Department, E.Salary
                FROM (
                      SELECT  ROW_NUMBER() OVER(ORDER BY ' + @sortBy + ') AS rowNumber, *
                      FROM    Employee
                     ) AS E
                WHERE  rowNumber >= ' + CONVERT(VARCHAR(9), @startIndex) + ' AND
                       rowNumber <  ' + CONVERT(VARCHAR(9), @upperBound)
  EXEC (@sqlStatement)
 

Me podeis hechar una ayudita a ver si logro solucionar este fallo?

Muchas gracias por todo y un Saludo!

[gnzsoloyo]

Lo que estás tratando de crear es una sentencia preparada, y eso no se hace en MySQL con variables privadas de SP sino con variables de usuario, las cuales se usan sin declarar, pero se identifican por llevar un "@" como prefijo.
Es decir, el problema es que estás usando mucho T-SQL de MS SQL Server, en lugar de PL de MySQL. Lo que debes revisar es el manual de referencia de MySQL y ver cómo se hacen aquellas cosas que no pertenecen al estandar ANSI SQL, como es el caso de las funciones de conversión, stored sentences, y demás:
Por ejemplo:
- No existe en MySQL el tipo de dato NVARCHAR, sino el VARCHAR.
- Los encadenamientos de texto se generan con CONCAT(), separando por comas los valores a encadenar, porque no tiene un límite dado para los grupos de texto.
- Las conversiones entre un tipo de dato y oro se hacen con CAST() y no con CONVERT porque el segundo de usa para conversiones de base y no de tipo. Algunas conversiones se las puede hacer implícitamente, como es el caso de las te cadena.
- IF... THEN/ ELSE/ END IF, se usa completo y no es una función. Para eso existe la función IF(condicion, true out, false out).
- Para asignar un valor de un SELECT a una variable o usas SET o usas INTO
- Hay funciones que no existen en MySQL, porque son propias de otro DBMS, como ROW_NUMBER() y OVER(ORDER BY value). Si puedes explicar lo que buscas, se puede resolver el problema.

Esto está un poco más cerca, pero aún no se puede ejecutar:

Código MySQL:

DELIMITER $$
 
CREATE PROCEDURE `galiciaparty`.`spGetAllEventos` (IN startIndex INT, IN pageSize INT, IN sortBy varchar(30), OUT totalEventos INT)
BEGIN
    DECLARE upperBound INT;
    SET startIndex  = IF(startIndex  < 1, 1, startIndex);
    SET pageSize = IF(pageSize < 1, 1, pageSize);
    SET upperBound = startIndex + pageSize;
    SET totalEventos=(SELECT Count(*) FROM gp_eventos_prin);
    SET @sqlStatement =concat('SELECT E.id_evento, E.titulo, E.fecha_evento, E.lugar ',
                       'FROM (SELECT  ROW_NUMBER() OVER(ORDER BY ', sortBy, ') AS rowNumber ',
                       'FROM    Employee) AS E  WHERE  rowNumber >= ', startIndex, 
                       ' AND rowNumber <  ', upperBound)
  exec (sqlStatement);
END$$
 
DELIMITER ;
 

Para completarlo, hace falta saber qué es lo que quieres obtener con esto:

Código MySQL:

SELECT  ROW_NUMBER() OVER(ORDER BY ' + @sortBy + ') AS rowNumber, * FROM    Employee
 

para ver cómo obtenerlo en MySQl.

[Baldur]

Madre mía, muchísimas gracias por toda esa explicación! Curradísimo el post!

Sabes que pasa, que a mi cuando me enseñaron SQL fue en el 2007/2008 y sobre SQL Server 2000, y desde aquella no he vuelto a hacer stored procedures ni nada que no fuesen consultas puras y duras...y claro..se nota jajaja.

Lo que intento hacer con todo esto es obtener el número de registros a paginar(para crear el control númerico de páginas) y además paginar los resultados.
El proyecto lo estoy haciendo con C#.NET, pero la BD es MySQL.

Me indicaron que le echase un vistazo a este artículo: http://dotnetslackers.com/articles/g...-GridView.aspx

Y estaba intentando implementarlo tal cual en mi proyecto, para comprobar bien como funciona y luego hacer las modificaciones pertinentes para dejarlo todo Ok...

Saludos y gracias por la ayuda!!

[Baldur]

Alguna idea para solucionar este problema de portar de SQL Server a MySQL este stored procedure??

Necesito solucionarlo cuanto antes...

Muchas Gracias por la ayuda y un Saludo a todos!

[Baldur]

Mirar, creo que he solucionado el problema del ROW_NUMBER() OVER(-------)

Código MySQL:

DELIMITER $$
 
CREATE PROCEDURE `galiciaparty`.`spGetAllEventos` (IN startIndex INT, IN pageSize INT, IN sortBy VARCHAR(30), OUT totalEventos INT)
BEGIN
* * DECLARE upperBound INT;
* * SET startIndex *= IF(startIndex *< 1, 1, startIndex);
* * SET pageSize = IF(pageSize < 1, 1, pageSize);
* * SET upperBound = startIndex + pageSize;
* * SET totalEventos=(SELECT Count(*) FROM gp_eventos_prin);
    SET @cnt = 0;
* * SET @sqlStatement =CONCAT('SELECT E.id_evento, E.titulo, E.fecha_evento, E.lugar
* * * * * * * * * * * *        FROM (SELECT id_evento, @cnt := (@cnt + 1) AS row_number
                                     FROM gp_eventos_prin
                                     ORDER BY ',sortBy,')) AS E *
                               WHERE *rowNumber >= ', startIndex,' AND rowNumber < *', upperBound,')'
* exec (sqlStatement);
END$$
 
DELIMITER ;
 

La solución la "He obtenido" de esta web: http://www.thingy-ma-jig.co.uk/blog/...alphabetically

Pero el MySQL WorkBench me sigue diciendo que hay "algún error" y dice:
SQL Syntax Error near: 'DECLARE upperBound INT;
* * SET startIndex *= IF(startIndex *< 1, 1, start'

Y marca como un cuadradito rojo en la línea del DECLARE.

Ahora sí que ya no sé que hacer...

Alguna idea??

Gracias por todo y un Saludo!