When I try to simplify the question :

DECLARE @empId VARCHAR

SET @empId ='RD11'

SELECT report_number FROM navsys.personal_exp_report_table
WHERE employee_id = 'RD11' order by report_number

select @empID

SELECT report_number FROM navsys.personal_exp_report_table
WHERE employee_id = @empId order by report_number

I got :

report_number
-------------
8
9
10
11
12
13
14
15
16

(9 row(s) affected)


----
R

(1 row(s) affected)

report_number
-------------

(0 row(s) affected)

So What I need to do is to re-declare
DECLARE @empId VARCHAR(30)

Thanks for all your attention anyway.