I have very often seen that people are so used to with traditional methods and hence never think in terms of set based programming, they used to write code with evergreen traditional method which people used to use in sequential language like C or many be ASP etc.
Let us see this practically with an example. Create one sample table with row given in following TSQL Script.
CREATE TABLE Employee (ID INT IDENTITY(1,1) , Name VARCHAR(10) , Dept VARCHAR(10) ) GO INSERT INTO Employee SELECT 'Ritesh','DBA' UNION ALL SELECT 'Rajan','Dev' UNION ALL SELECT 'Bihag','Net' GO
Now we have situation that if we pass “ALL” from application, it should show data from all department otherwise it should show data from the specific department which is provided in the variable. Let us see method one where people used to use IF condition to check value and have two separate SELECT statement. I don’t prefer this one, why we should write two SELECT statement whereas we can write only single. If we have many conditions, we have to repeat SELECT query in this method which is not preferable from the maintenance point of view.
--Method 1: DECLARE @Dept VARCHAR(10) SET @Dept = 'ALL' IF @Dept='ALL' BEGIN SELECT * FROM Employee END ELSE BEGIN SELECT * FROM Employee WHERE Dept=@Dept END
There are some people who uses method 2 where they take value in temporary table to find filter value and pass it to main SELECT clause so in this method you will have only one main SELECT clause but this is also less preferable method as it has multiple SELECT statement in set along with temporary table which affect performance adversary in most cases so don’t use temporary table unless there is no way around.
--Method 2: DECLARE @Dept VARCHAR(10) SET @Dept = 'ALL' IF OBJECT_ID('tempdb..#DeptList') IS NOT NULL DROP TABLE #DeptList CREATE TABLE #DeptList ( Dept VARCHAR(10) ) IF @Dept='ALL' BEGIN INSERT INTO #DeptList (Dept) SELECT Dept FROM Employee END ELSE BEGIN INSERT INTO #DeptList (Dept) SELECT Dept FROM Employee WHERE Dept=@Dept END SELECT * FROM Employee WHERE Dept IN (SELECT Dept From #DeptList) GO /*method 3 which is my preferable shortest method in this kind of situation which has good performance as well when compare it with above two method.*/ --Method 3 DECLARE @Dept VARCHAR(10) SET @Dept = 'ALL' SELECT * FROM Employee WHERE Dept=CASE WHEN @Dept='ALL' THEN Dept ELSE @Dept END
Are you using any other method to achieve the task explained here? It is interesting to discuss…!!!!