Help with SQL query

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  July 5, 2011 at 2:23 am

  #1348102

  CELKO (7/4/2011)

  ---

  CREATE PROCEDURE Search_Personnel

  (@in_emp_id INTEGER, @in_emp_name VARCHAR(35),

  @in_emp_dept_nbr INTEGER, @in_emp_loc INTEGER)

  AS

  SELECT emp_id, emp_name, emp_dept_nbr, emp_loc

  FROM Personnel

  WHERE emp_id = COALESCE (@in_emp_id, emp_id)

  AND emp_name LIKE COALESCE ( '%'+ @in_emp_name +'%', emp_name)

  AND emp_dept = COALESCE (@in_emp_dept, emp_dept)

  AND emp_loc = COALESCE (@in_emp_loc,emp_loc);

  That'll work. I hope that good performance is not a requirement though as it most certainly won't have that.

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass