Your code will replace "Test" with "Dev" in the ServerType column for the entire table.

If you wish to only do the update where Company is "Blue", then you need to...

SSC has an article by Sean Smith with code for playing Battleships in SSMS

http://www.sqlservercentral.com/scripts/AI/109744/

Try this on for size

DECLARE @FormType varchar(50); -- Adjust data type and size to match [f1] + [f2] + [f3]

SET @FormType = ISNULL( (SELECT FormType = ltrim(RTRIM(ISNULL(f1,'')+ISNULL(f2,'')+ISNULL(f3,'')))
...

I suppose a code sample for the Dynamic SQL might help

DECLARE
    @Eid            ...

This is a classic Catch-All query.

• If you check your execution plan, you will note that you have all table/index scans and no seeks.  That is due to the fact...

All of those UNIONs are creating an insane query plan.

The following code will produce almost the same result, with a MUCH better query plan.

DECLARE @options int =...

IF (SELECT count(emp.name) FROM **** WHERE *** IN (***)) > 0
BEGIN
  SELECT 1;
END;
ELSE
BEGIN
  SELECT 0;
END;

FORMAT is a dog when comes to performance.

You would be better off using CONVERT.

The following 2 expressions return the same result.  Although formatting is best left to the presentation...

I stand to be corrected here, but NOLOCK on a #temptable has no effect.  The #temptable is local to your SPID, so there is nobody else reading or writing to...

Your SQL is not valid.

Your description of the problem does not make sense.

Kindly provide readily consumable sample data, your expected outcome, and a functioning SQL statement.

Shifting gears a bit, have a look at the original post on the following thread (no need to pile on.. I'm just confused) ...

https://www.sqlservercentral.com/forums/topic/conversion-failed-when-converting-the-varchar-value-to-data-type-int-4

The OP posted this early...

I would start by trying to replace

IF 0 < (SELECT COUNT(*)

with

IF EXISTS (SELECT 1

Next check your statistics.  the massive disparity between estimated and actual...

So, based on your sample data, what is your expected result?

Try

FOR stateID IN ( [36], [11] )

Because the logical query processing order will process the join before the where, it MIGHT improve performance slightly if you search for the Commodities before searching for the Suppliers.

            
November 11, 2019 at 1:14 pm
            
#3696695