All those UNION ALLs could also be an issue.  You may want to consider a Divide-n-Conquer path.

Create a #TempTable, then break the massive union all up, inserting each set of...

Secondly, this line in your WHERE clause will ALWAYS cause a full table/index scan.

and (a.BenefitsID=@BenefitsID OR @benefitId = 'ALL')

The reason for this is that SQL has to...

Firstly, your query is hitting the same table 8 times.  This can be vastly improved by using a cross-tab query to only hit the table once.  The following 2 queries...

I believe that APPLY was introduced in 2005

https://www.red-gate.com/simple-talk/sql/t-sql-programming/sql-server-apply-basics/

https://www.mssqltips.com/sqlservertip/1958/sql-server-cross-apply-and-outer-apply/

This is a duplicate post.

Original HERE

@DesNorton - I don't think sp_ExecuteSQL would actually diminish the danger. If the @WeatherFieldName variable had a semicolon in it, followed by a query - I believe it would...

Having a large list of fields, you are definitely going to have some ugly long code.

If SQL Injection is your only objection to Dynamic SQL, then you can mitigate the...

You can use OUTER APPLY

SELECT      [Car type]        = C.CarType
      ...

A small perf improvement on @shane.green code

INSERT INTO #FieldValues ( Field1, Field2, Field3 )
SELECT  Field1 = MAX( CASE
        ...

You have nothing that binds the 3 records together and nothing that orders the records.  So there is no way to consistently achieve what you are trying to do.

I'm too lazy to create tables to test.  But I believe that there are 2 ways that you could achieve this.

SELECT    Table1.EMPID
FROM  ...

SQL is not Excel.  By default, it cannot see the value in the previous or next row.

However, Window Functions are going to be your new best friend for this task,...

Deadlocks are not my strong point.  That said, deadlocks occur when 2 or more processes are waiting on each other to access the same resource.  The deadlocks can occur at...

This may be what you are looking for .

SELECT  *
FROM    COMPANY
WHERE   PRODUCTS IN ( 'Product1', 'Product2', 'Product3', 'Product4' );

If you are only interested in specific employees, then put the filter on the Employee table

WITH cteHours AS (
...
)
SELECT e.identifier
, AllHours = ISNULL(SUM(h.[HOURS]),0)/60.00
FROM cms.dbo.Employee AS e
LEFT JOIN...