Stored Procedure

See more:

SQL-Server-2005

SQL-Server

I have one table(EMPLOYEE),in that columns are (Emp_Id, First_Name, Last_Name, Gender, Mobile_No, Email_id, Blood_Group);

In my front end page having 4 text boxes that is emp_id, emp_name, mobile_no, blood_group.
(here search buttion),

Whenever we give id( or) name (or )mbno( or) bloodgroup(or) any combinations then result come with (Emp_Id, First_Name, Last_Name, Gender, Mobile_No, Email_id, Blood_Group)display.

I try some code but result is not coming.code is following
----------------------------

DELIMITER $$
CREATE  PROCEDURE sp_srch(in empid varchar(10), empname varchar(45),mobileno varchar(15),bloodgroup varchar(5))
BEGIN
    declare str varchar(255);
    declare str1 varchar(20);
    declare str2 varchar(20);
    set  str ="select Emp_Id,First_Name,Last_Name,Gender,Mobile_No,Email_id,Blood_Group from Employee where ";
    if(empid!= '') then
       set   str= Concat(str ,"Emp_ID like '%", empid ,"%'and ");
    end if;
    if(empname!='') then
       set   str= Concat(str , "First_Name like '%" , empname ,"%'and ");
    end if;
    if(mobileno!='') then
       set  str= Concat(str , "Mobile_No like '%" , mobileno ,"%'and ");
    end if;
    if(bloodgroup!='') then
       set  str= Concat(str ,"Blood_Group like '" , bloodgroup ,"%' ");
    end if;
    set str1= (SUBSTR(str,-6));
    set str2 = (SUBSTR(str,-4));

    if(str1='where') then
     set str= replace(str,'where','');
      end if;
    if(str2='and') then
      set str= replace(str,' and','');
       end if;
select str;

END $$
DELIMITER ;