0

Can anyone translate me this MSSQL expression to MySQL?

CREATE PROCEDURE `spGetEmployees`
@StartIndex int,
@MaximumRows int
as
BEGIN
    select EmployeeID,
           Name,
           Gender, 
           City,
           StartDate
      from (select row_number()
              over (order by EmployeeID)
                as RowNumber,
                   EmployeeID,
                   Name,
                   Gender,
                   City,
                   StartDate
              from tblEmployee) Employees
     where RowNumber >= @StartIndex
       and RowNumber < (@StartIndex + @MaximumRows);
END

tblEmployee is a real table and Employees is derived table.

djole351
  • 11
  • 3
  • 2
    Yes, we could. But this is not a coding service: what specific problem did you find when trying to do so yourself? – Bohemian Apr 19 '14 at 02:42
  • You may want to look at [ROW_NUMBER() in MySQL](http://stackoverflow.com/questions/1895110/row-number-in-mysql) for creative ideas since MySql does not support `analytic functions`. – PM 77-1 Apr 19 '14 at 02:44
  • If tblEmployee is a temp table, you haven't provided enough information. – Dan Bracuk Apr 19 '14 at 03:10
  • The best explanation what I tried to do is: I tried to do this ASP.net exercise but in mySql: https://www.youtube.com/watch?v=Zp-aYblaq2I&list=PL6n9fhu94yhW1NryGv6LxX4U4b07T4RlI – djole351 Apr 19 '14 at 08:27

1 Answers1

0

The general syntax for mysql procedures is

CREATE PROCEDURE storedProcedureName( IN someString VarChar(150) )
BEGIN
  -- Sql queries goes here
END

IN your case, your procedure would be something like:

CREATE PROCEDURE spGetEmployees( IN StartIndex int, IN MaximumRows int)
BEGIN

select EmployeeID, Name, Gender, City, StartDate from
(select row_number() over (order by EmployeeID) as RowNumber, EmployeeID, Name,
    Gender, City, StartDate
from tblEmployee)  Employees
-- this part you may want to use limit or other mysql functions
--where RowNumber >= StartIndex and RowNumber < (StartIndex + MaximumRows);

END

If you also need, the basic syntax of temporary tables in MySQL is something like:

CREATE TEMPORARY TABLE tableName(
emp_id VARCHAR(10),
 emp_Name VARCHAR(50),
emp_Code VARCHAR(30),
emp_Department VARCHAR(30)
);
bovino Marcelo Bezerra
  • 2,042
  • 16
  • 28
  • Thank you Marcelo but I already tried to do this. I's not working. Please look at : https://www.youtube.com/watch?v=Zp-aYblaq2I&list=PL6n9fhu94yhW1NryGv6LxX4U4b07T4RlI I try to do this but in mySql – djole351 Apr 19 '14 at 08:32
  • What exactly is not working? The creation of the procedure? Some problem with the creation of a temporary table? What error message are you receiving? Please, be more specific and will be able to help you more. – bovino Marcelo Bezerra Apr 19 '14 at 17:44
  • First, I was wrong about table tblEmployee. It is a real table and Employees is derived table. The goal of procedure is to display records from 1 to 3 when I call spGetEmployees(1,3). I wrote a procedure like you but I have included the part with 'where'. The Error is on '(' right next to the 'оrdеr'. It's says: unexpected '(' , expecting UNION_SYM or ')' – djole351 Apr 21 '14 at 11:25