There was a time where I used to believe that the worst possible way one could retrieve rows from a table was to SELECT the entire table, iterate through the rows, and test a column to see if it matched some critera; all the while ignoring the WHILE clause. Ahh, how naive I was back then. This procedure from Jeremy Price makes a WHERE-less SELECT look like a celebrated best practice ...

CREATE PROCEDURE sp_SearchProjects
( 
  @sProjectID VARCHAR(50), 
  @sProjectName VARCHAR(50), 
  ...
) AS BEGIN

  DECLARE @SearchTable TABLE 
    ( sProjectID VARCHAR(50), sProjectName VARCHAR(50), ..., iFoundFlag INT )

  INSERT INTO @SearchTable (sProjectID, sProjectName, ..., iFoundFlag)
  SELECT sProjectID, sProjectName, ..., 0
    FROM tbl_Projects

  IF LEN(@sProjectID) > 0
  BEGIN
   UPDATE @SearchTable 
      SET iFoundFlag = 1
     FROM @SearchTable a
    WHERE LTRIM(RTRIM(a.sProjectID)) = LTRIM(RTRIM(@sProjectID))

   DELETE @SearchTable
     FROM @SearchTable a
    WHERE a.iFoundFlag=0

    --Set the found flag back to Not found for the next search.
   UPDATE @SearchTable 
      SET iFoundFlag = 0
     FROM @SearchTable
  END

  --ED: Snipped 4 more search criteria

  IF LEN(@sProjectName) > 0
  BEGIN
   UPDATE @SearchTable 
      SET iFoundFlag = 1
     FROM @SearchTable a
    WHERE a.sProjectName LIKE @sProjectName;

   DELETE @SearchTable
     FROM @SearchTable a
    WHERE a.iFoundFlag=0

    --Set the found flag back to Not found for the next search.
   UPDATE @SearchTable 
      SET iFoundFlag = 0
     FROM @SearchTable
  END

  --return results
  SELECT * FROM @SearchTable

END
[Advertisement] BuildMaster allows you to create a self-service release management platform that allows different teams to manage their applications. Explore how!