I have two tables. one is local and other one is foreign. so what I want to do is to give row numbers after joining two tables using stored procedure.
First I want to get same number of column from two tables and after that I want combine as a one table and give row numbers.
below is my query.
select (@row_number:=@row_number + 1) as number,
(select ID,title ,last_name,first_name
It seems that you are using MySQL, not SQL Server, and try to emulate row numbers, as shown eg in this duplicate question. This is trivial to do in SQL Server using the ROW_NUMBER function, as shown by @Prdp's answer.
MySQL though doesn't have the ranking, analytic or windowing functions found in other databases. Such functions can be emulated in a very limited fashion by using non-standard SQL tricks, as shown in the linked question.
Such tricks are very limited though. A typical use of
ROW_NUMBER is to rank records inside a group, eg top 10 salesmen by region. It's not possible to do that with the
@curRow := @curRow + 1 trick. There are performance implications as well. This trick will only work if the rows are processed sequentially.
In the question's case, a MySQL query would probably look like this:
SELECT l.ID, l.title, l.last_name, l.first_name, @curRow := @curRow + 1 AS row_number FROM ( select ID,title,last_name,first_name from local UNION ALL select ID,title ,last_name,first_name from foreign ) l JOIN (SELECT @curRow := 0) r
The trick here is that
JOIN (SELECT @curRow := 0) creates the variable
@curRow with an initial value of 0 and returns its value. The database will take the query results and for each row, it will increase the variable and return the increased value. This can only be done at the end and forces sequential processing of the results.
JOIN (SELECT @curRow :=0) r you just avoid creating the variable in a separate statement.