This is how the query should look like:

SELECT inv.ProductID, ISNULL(inv.QTYonHand, 0)
FROM Inventory AS inv
WHERE inv.WarehouseID = 1
AND EXISTS ( select * 
from Product P 
inner join CatProduct...

A significant part of both PROD and test queries if Key Lookup on Product table. It might affect the plan selection too.

Including ProductTypeId into the index IX_Active_ActiveOnline might help a...

People don't understand that a CTE is NOT executed first and then the rest of the query works against that as if it were a table with the possible...

1st, as it's said, round() with the parameter,

2nd, FLOOR(),

3rd, if you store numbers with fixed decimal places you should be using DECIMAL data type for that, not REAL...

Don't implement is as a table.

make it into a view. May be dynamically created view, to handle changing requirements for the column set.

Then you're not gonna need to do horribly...

I’m not asking about the resultset, I know the entry must be there.

The question is - where do you get it from? I mean - FROM ( the keyword)

1st, as it's said, round() with the parameter,

2nd, FLOOR(),

3rd, if you store numbers with fixed decimal places you should be using DECIMAL data type for that, not REAL or FLOAT.

Try using GROUP BY ProductId in CTE query

@ktflash..thanks but...your code returns well when the job exists but....

you code returns nothing if the job does not exist.

My requirement is if the job does not exist it should...

It's not EF, it's how SQL server handles procedures.

Metadata is retrieved during parsing time. Temporary tables are created and possibly modified in run time.

therefore, SQL Server has no means to...

I posted the solution in your previous thread, Scott posted pretty much the same solution here, just above. The only difference is that I used one more left join -...

Did you read my answer in your previous thread for this problem?

First, we're good boys and girls here, so we don't hardcode data. All the database/job names we put in some kind of table - temporary or permanent.

then we use outer...

Joining a remote table is not a good move.

it has to be fully loaded to the local server, and then every row of it should be compared to every row...

Can you post the actual query please?