Just a point to note here:   The way that dynamic SQL works, you are going to get MULTIPLE queries and execution plans from the various combinations of parameters.    Some...

There is a difference between returning a null value and returning no rows.  What would the batchID be if there are no rows for a given batchID?

If you have another...

Lots of ways to skin this cat.    You can join to the tblOperator table twice (one of them should be a match. ) and use COALESCE to get the...

I totally agree with Grant on searching against a function leading to table scans and the potential for an injection attack.    Even though you are using sp_ExecuteSQL, you are...

The key to your problem is that you want two rows to be generated from a single rows.    My favorite technique for accomplishing this is to use CROSS APPLY...

I agree.   Nested loops are not considered RBAR.    There are alternative ways to code for getting only the latest row, but the best thing you can do for performance...

Summing case statements instead of doing a PIVOT, is referred to as a cross tab.

https://www.sqlservercentral.com/articles/cross-tabs-and-pivots-part-1-%e2%80%93-converting-rows-to-columns-1

Also, I hear you talking about the overhead of a CROSS APPLY.    I've...

I feel compelled to confess that the calendar table I use has all the dates.    I'm actually delighted that the "Some people prefer"  invited such rebuttals.    Also those...

Apparently, I am serving SPAM too.    Just noticed this on the 2016 development forum.

In: Pentobarbital for sale https://hospitableending.com/?

By: The Dixie Flatline

I've heard of "Phenobarbitol", but not Pentobarbital.   Go figure.

Scott, just asking for clarification.   You spoke of finding the UNPIVOT syntax kludgy (and I agree) but you posted the "crosstab" syntax which is the old-school way to PIVOT rows...

It's easy and flexible to include weekends in a Calendar table, usually with two flags one that says weekends, one that says holidays.  Sometimes Christmas falls on a Saturday, but...

I am just editing this message  because the more you look the stranger things seem.    I would have agreed with your comment about nested transactions.   But look what happens...

One comment.    For your example #10, deleting a range of rows,  you used the following

DELETE FROM EMPLOYEE WHERE FName LIKE '%Kokou%';

Because the search argument began with...

I wouldn't expect to have to use two ROLLBACKS.   I thought one ROLLBACK set @@TRANCOUNT to zero.   (See below.)

In any event, I've never seen a need to use SET IMPLICIT...

declare @Sample_Data table 
(
id varchar(20) ,
Eid varchar(20),
typ varchar(20),
dur float
)

insert into @Sample_Data (id,Eid,typ,dur) VALUES ('1','e1','t1','18.00');    
insert into @Sample_Data (id,Eid,typ,dur) VALUES ('2','e2','t1','14.00');
insert into @Sample_Data (id,Eid,typ,dur) VALUES...