Writing good TSQL vs Bad

By | October 15, 2019

Saw this question on the forum and wanted to highlight how easy it is to over complicate a simple query.

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/75264de5-9a32-40f4-b9ce-e430d77b77fe/case-when-is-not-working-please?forum=transactsql

A perfect example of Programmatic access vs Set based querying. The developer has an unhealthy obsession with CASE statements and as you can see from the post he is now facing issues as a result

Here is the original query written by the developer

SELECT

[LogID]

,[AttendanceDate]

,Employee.DepotNo

,Employee.DepartmentID,

ISNULL(

    (SELECT MinClockin FROM [dbo].[goViewEmpMinMaxClocking]

        WHERE EmpCode = Employee.EmployeeID AND ClockedDay = @dateclocked),’00:00′)

        ClockedIn

,ISNULL(

    (SELECT MaxClockOut FROM [dbo].[goViewEmpMinMaxClocking]

        WHERE EmpCode = Employee.EmployeeID AND ClockedDay = @dateclocked),’00:00′) ClockedOut,

CASE

                WHEN [Minclockin] IS
NULL
THEN 0 ELSE 1    END
AS MinclockinValue

                FROM [dbo].[goViewEmpMinMaxClocking]     WHERE EmpCode = Employee.EmployeeID AND ClockedDay = @dateclocked

FROM dbo.goEmployee Employee

     INNER
JOIN [dbo].[goAttendancelog] Attendancelog ON Employee.EmployeeID = Attendancelog.EmployeeID

 

And here is the much simple query when written with some set based thought process.

 

select

[LogID]

,[AttendanceDate]

,Employee.DepotNo

,Employee.DepartmentID

,
ISNULL(MinClockin ,
’00:00′)
as MinClockin

,
ISNULL(MaxClockOut ,
’00:00′)
as MaxClockOut

,IIF([Minclockin] IS
NULL,0 , 1 )
as MinclockinValue

from dbo.goEmployee Employee

inner
join [dbo].[goAttendancelog] Attendancelog

ON Employee.EmployeeID = Attendancelog.EmployeeID

LEFT
OUTER
JOIN [dbo].[goViewEmpMinMaxClocking] ve

on ve.EmpCode =Employee.EmployeeID and ClockedDay = @dateclocked

 

 

Category: Uncategorized

About opsadmin

This post was written by Jayanth Kurup. A Microsoft SQL Server Consultant and Trainer based out of Bangalore, India. Jayanth has been working on MS SQL Server for over 15 years. He is a performance tuning and Business Intelligence expert. Having worked with companies like Microsoft, DELL, Wells Fargo, Thomson Reuters and many other fortune 100 companies. Some other technologies Jayanth works on include Microsoft Azure, PowerBI, Python and AWS. When he isn’t consulting or training, Jayanth like to travel, paint and read. He is also very active in social causes and the founder of Enabled Business Solutions. Visit his company by clicking the link in the menu or email him directly.