No, the syntax is somewhat off. A CASE expression must evaluate to a single value.

For what you're doing, try something like this instead:

IF @Client = 'Winco Foods' AND EXISTS(

SELECT...

Also, for best performance, specify the least likely to match condition first. If one (or more) of the AND conditions are going to be false, you want SQL to...

Create a separate index of just the IDs. Then select a random ID using ORDER BY NEWID(), then use that ID to get the rest of the columns. ...

Do you want the concatenation to be able to cause a match? For example, if the PartnerName ends in "f", and the Address begins with "arm", should that cause...

If the tables are indexed on id, you just have to force SQL to use the obvious MERGE join it should have been using anyway(!) 😀 :

DROP TABLE #TABLE1

DROP TABLE...

Sean Lange (12/8/2015)

---

ScottPletcher (12/7/2015)

---

In this case, there's no real need for CTEs or UNIONs either, just standard FULL joins:

SELECT

COALESCE(t1.PRODUCTID, t2.PRODUCTID, t3.PRODUCTID) AS PRODUCTID,

...

An INSERT to a table won't try to return a result set ... unless you're missing a:

SET NOCOUNT ON

at the start of the trigger. Verify that statement is present.

Edit:...

In this case, there's no real need for CTEs or UNIONs either, just standard FULL joins:

SELECT

COALESCE(t1.PRODUCTID, t2.PRODUCTID, t3.PRODUCTID) AS PRODUCTID,

COALESCE(t3.STATUS,...

SQLPain (12/7/2015)

---

Thanks Scott, I appreciate your help, but not passing the year, wouldn't it do a count of all the years for that month, for example in future if I...

Sure, fine, I give up.

Just be aware that using MONTH or any other function on a column could give you horrible performance.

Month is a datetime type: you need to provide a full date:

@month = '20151001'

The CROSS JOIN is just used to assign an alias name to the constructed date.

SQLPain (12/7/2015)

---

Scott, would the following work, its kind of giving me the correct results:

ALTER PROC Applications

(

@Month AS DATETIME,

@Year AS DATETIME

)

AS

BEGIN

LEFT JOIN (SELECT COUNT(CA.app_id) AS Approved_Count, SO.source_id

...

SQLPain (12/7/2015)

---

Scott, would the following work, its kind of giving me the correct results:

ALTER PROC Applications

(

@Month AS DATETIME,

@Year AS DATETIME

)

AS

BEGIN

LEFT JOIN (SELECT COUNT(CA.app_id) AS Approved_Count, SO.source_id

...

You'll get best performance only from properly clustering the underlying table. Then either a direct query or a view should perform just fine.