searching from database

• jmamphekgo

  SSC Enthusiast

  Points: 103

  More actions

  July 4, 2008 at 8:21 am

  #187798

  Hi

  I am trying to search names and surnames from database.For example i can search using only name and it will display all matching names in the database or i can use surname and it will display all matching surnames in the database or i can use both name and surname and it will display only exact name.check my select statement below

  SELECT Emp_No,Emp_Name,Emp_Surname FROM Employees WHERE Emp_Name LIKE @Emp_Name + '%' AND Emp_Surname LIKE @Emp_Surname+'%'AND Emp_Name LIKE @Emp_Name + '%' OR Emp_Surname LIKE @Emp_Surname+'%'

• Ken Simmons

  SSCertifiable

  Points: 7822

  More actions

  July 4, 2008 at 8:34 am

  #838370

  What you can do is if the parameter is null set it to equal itself. If you pass a parameter it will use the value otherwise it will ignore it.

  SELECT Emp_No,Emp_Name,Emp_Surname

  FROM Employees

  WHERE Emp_Name LIKE ISNULL(@Emp_Name + '%', Emp_Name) AND

  Emp_Surname LIKE ISNULL(@Emp_Surname + '%', Emp_Surname)

• Vijaya Kadiyala

  SSCrazy Eights

  Points: 9149

  More actions

  July 4, 2008 at 9:21 am

  #838384

  HI,

  You can also try out Dyanmic SQL query in T-SQL

  Check out the below link

  http://www.sommarskog.se/dyn-search.html#dynsql

  Thanks -- Vj

  http://dotnetvj.blogspot.com

  Thanks -- Vijaya Kadiyala
  www.dotnetvj.com
  SQL Server Articles For Beginers