Koen Verbeeck (6/17/2014)

---

Sean Lange (6/17/2014)

---

You could make this a lot simpler and skip the cte entirely.

SELECT @Total = count(CAST(id AS VARCHAR(60))) FROM cat_transaction

EXCEPT

SELECT ext_reference FROM Patricia.dbo.BUDGET_LINE

I beg to differ:

SELECT ID = 1

INTO #Temp1

UNION ALL

SELECT 2

UNION ALL

SELECT 3

UNION ALL

SELECT 4

UNION ALL

SELECT 5;

SELECT ext_reference = 5

INTO #Temp2;

SELECt * FROM #Temp1;

SELECt * FROM #Temp2;

DECLARE @Total INT;

SELECT @Total = COUNT(CAST(ID AS VARCHAR(60)))

FROM #Temp1

EXCEPT

SELECT ext_reference

FROM #Temp2;

SELECT @Total;

Msg 141, Level 15, State 1, Line 16

A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.

Msg 10734, Level 15, State 1, Line 22

Variable assignment is not allowed in a statement containing a top level UNION, INTERSECT or EXCEPT operator.