Common table expression giving error

Question

DECLARE @UDFLabel1 VARCHAR(50),
        @UDFLabel2 VARCHAR(50),
        @UDFLabel3 VARCHAR(50),
        @UDFLabel4 VARCHAR(50),
        @UDFLabel5 VARCHAR(50)

SELECT @UDFLabel1 = UserDefinedFieldName01, 
       @UDFLabel2 = UserDefinedFieldName02,
       @UDFLabel3 = UserDefinedFieldName03,
       @UDFLabel4 = UserDefinedFieldName04,
       @UDFLabel5 = UserDefinedFieldName05
FROM DataSourceGroup AS DSG
JOIN DataSource AS DS
ON DS.DataSourceGroupID=DSG.DataSourceGroupID
WHERE DS.DataSourceID=@DataSourceID

WITH UDFLabel ( ID, Label ) 
AS ( SELECT 1, @UDFLabel1
     UNION 
     SELECT 2, @UDFLabel2
     UNION 
     SELECT 3, @UDFLabel3
     UNION 
     SELECT 4, @UDFLabel4
     UNION 
     SELECT 5, @UDFLabel5
) 
SELECT ID, Label
FROM UDFLabel
WHERE Label IS NOT NULL

Answer 1

Assuming you mean the error message:

Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

Then do what it says; use a semicolon to terminate the previous expression...

[snip]
WHERE DS.DataSourceID=@DataSourceID
;

WITH UDFLabel ( ID, Label ) 
AS ( SELECT 1, @UDFLabel1
     UNION 
[snip]

The ; is important, and makes it work.

I think I'd use a table-variable (or similar) for this scenario, though.