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!