Stored Procedure
-
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 boxies.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 ;
-
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 boxies.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 ;
EXEC (str)
or
sp_executesql str
But both are awful ways to implement a search query - and im betting you're passing textbox values directly into the SP - opening you up to sql injection attacks. You should be doing it like the following, passing null for any params where you dont have a value (or not passing them at all, and taking the default, null)
CREATE PROCEDURE sp_srch(
@empid varchar(10) = NULL,
@empname varchar(45) = NULL,
@mobileno varchar(15) = NULL,
@bloodgroup varchar(5)) = NULL
BEGIN
select Emp_Id,First_Name,Last_Name,Gender,Mobile_No,Email_id,Blood_Group
from Employee
where (@empID IS NULL OR emp_id like '%' + @empID + '%')
and (@empName IS NULL OR first_name like '%' + @empName + '%')
and (@mobileno IS NULL OR mobile_no like '%' + @mobileno + '%')
and (@bloodgroup IS NULL OR blood_group like '%' + @bloodgroup+ '%') -
EXEC (str)
or
sp_executesql str
But both are awful ways to implement a search query - and im betting you're passing textbox values directly into the SP - opening you up to sql injection attacks. You should be doing it like the following, passing null for any params where you dont have a value (or not passing them at all, and taking the default, null)
CREATE PROCEDURE sp_srch(
@empid varchar(10) = NULL,
@empname varchar(45) = NULL,
@mobileno varchar(15) = NULL,
@bloodgroup varchar(5)) = NULL
BEGIN
select Emp_Id,First_Name,Last_Name,Gender,Mobile_No,Email_id,Blood_Group
from Employee
where (@empID IS NULL OR emp_id like '%' + @empID + '%')
and (@empName IS NULL OR first_name like '%' + @empName + '%')
and (@mobileno IS NULL OR mobile_no like '%' + @mobileno + '%')
and (@bloodgroup IS NULL OR blood_group like '%' + @bloodgroup+ '%')J4amieC wrote:
You should be doing it like the following, passing null for any params where you dont have a value (or not passing them at all, and taking the default, null)
Or passing 0 if is can be assumes that null and 0 are equivalent, this is then more fault tolerant
(ISNULL(@empID, 0) = 0 OR emp_id like '%' + @empID + '%')Never underestimate the power of human stupidity RAH
-
J4amieC wrote:
You should be doing it like the following, passing null for any params where you dont have a value (or not passing them at all, and taking the default, null)
Or passing 0 if is can be assumes that null and 0 are equivalent, this is then more fault tolerant
(ISNULL(@empID, 0) = 0 OR emp_id like '%' + @empID + '%')Never underestimate the power of human stupidity RAH
Mycroft Holmes wrote:
Or passing 0 if is can be assumes that null and 0 are equivalent, this is then more fault tolerant
All the parameters were varchar, so passing zero is impossible. However your point is a valid one - for numeric optional input I would most certainly have the default as zero and check for nulls in the way you suggested.
-
Mycroft Holmes wrote:
Or passing 0 if is can be assumes that null and 0 are equivalent, this is then more fault tolerant
All the parameters were varchar, so passing zero is impossible. However your point is a valid one - for numeric optional input I would most certainly have the default as zero and check for nulls in the way you suggested.
J4amieC wrote:
ll the parameters were varchar
Did not even look at that :-O I assumed anything with an ID is numeric and this structure is almost rote for me.
Never underestimate the power of human stupidity RAH