How to get row number using sql stored procedure

I have two tables. One is local and other is foreign. so, what I want to do is to give row numbers after joining two tables using stored procedure. First I need to get same number of column from two tables and after that I need to combine as a one table and give row numbers. below is my query.

set @row_number=0;

select (@row_number:=@row_number + 1) as number, 
(
select a.*
from 
(select ID,title,last_name,first_name
from local
)
a
union all
select b.*
from
(select ID,title ,last_name,first_name
from foreign
)
b
)
;

Could anyone please tell me what's the wrong with it?

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.

UPDATE: ROW_NUMBER() is available in MySQL 8.0.

MySQL 8.0 added the ranking functions found in other databases, so now it's possible to write :

SELECT  l.ID,
        l.title,
        l.last_name,
        l.first_name,
        ROW_NUMBER() OVER(ORDER BY l.ID) as number
FROM    (   select ID,title,last_name,first_name
            from local
            UNION ALL
            select ID,title ,last_name,first_name
            from foreign
         ) l

Only use the following trick if you're on 5.x and can't upgrade to 8.x

MySQL before 8.0 didn'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.

By using JOIN (SELECT @curRow :=0) r you just avoid creating the variable in a separate statement.