View isn't getting created

Question

Can you guys please explain to my why I am having the error message when I am trying to create this view.

The error that I get is:

Msg 156, Level 15, State 1, Procedure ComplianceView, Line 2
Incorrect syntax near the keyword 'declare'.

The script that I am using is :

CREATE VIEW ComplianceView 
AS
    DECLARE @training AS TABLE (forename NVARCHAR(50), 
                                surname NVARCHAR(50), 
                                module NVARCHAR(100), 
                                date DATETIME)

    INSERT INTO @training (forename, surname, module, date)
        SELECT
            [First Name], surname, module, [completion date]
        FROM
            dbo.[report$]

        UNION ALL

        SELECT
            [First Name], surname, module, [completion date]
        FROM
            [Import Training]

    SELECT DISTINCT 
        l.[employee id],
        l.firstname,
        l.surname,
        l.[job role],
        l.manager,
        m.Mandatory,
        l.location,
        t.module,
        CONVERT(NVARCHAR(35), MAX(t.date), 106) AS LastCompleted,
        CONVERT(NVARCHAR(35), DATEADD(YY, m.[Frequency of training], MAX(t.date)), 106) AS RenewalDate,
        CASE
           WHEN DATEADD(YY, m.[Frequency of training], MAX(t.date)) > GETDATE() 
              THEN 'In Date'
              ELSE 'Out of date' 
        END AS [Compliant]
    FROM
        lookup l 
    JOIN
        @training t ON l.FirstName = t.forename 
                    AND l.Surname = t.surname
    LEFT JOIN
        Mandatory m ON m.[Course Title] = t.module 
                    AND m.[Job Role] = l.[Job Role]
    WHERE
        t.module IN ('Customer Care', 'Equality and Diversity', 'Fire Safety',
                     'Infection Control (Non Clinical)', 'Information Governance', 
                     'Moving and Handling (Non Clinical)', 'Prevent',
                     'Safeguarding Adults (Level 1)', 'Basic Life Support',
                     'Chaperoning', 'Consent', 'On Call Training', 
                     'Safeguarding Children (Level 3)', 'Infection Control (Clinical)',
                     'Moving and Handling (Clinical)')
    GROUP BY
        l.[employee id], l.firstname, l.surname,
        l.[job role], l.manager, 
        t.module,
        l.location,
        m.[Frequency of training], m.mandatory
    ORDER BY
        m.Mandatory DESC

The script works when I am looking at it without the view syntax.

Answer 1

As mentioned in comments you can only have a query within a view. You can do this with a CTE (Common Table Expression) instead of using a table variable.

Create view ComplianceView as
WITH training AS (
    select [First Name], surname, module, [completion date]
    from dbo.[report$]

    union all 

    select [First Name], surname, module, [completion date]
    from [Import Training]
)
select distinct l.[employee id],
l.firstname,
l.surname,
l.[job role],
l.manager,
m.Mandatory,
l.location,
t.module,
Convert(nvarchar(35),MAX(t.date),106) as LastCompleted,
Convert(nvarchar(35),DATEADD(YY,m.[Frequency of training],MAX(t.date)),106) 
as RenewalDate,
Case when DATEADD(YY,m.[Frequency of training],MAX(t.date)) > getdate() then 
'In Date'
else 'Out of date' end as [Compliant]
from lookup l join training t on l.FirstName = t.forename and l.Surname = 
t.surname
left join Mandatory m on m.[Course Title] = t.module and m.[Job Role] = l. 
[Job Role]
where  t.module in ('Customer Care','Equality and Diversity','Fire 
Safety','Infection Control (Non Clinical)','Information Governance','Moving 
and Handling (Non Clinical)',
'Prevent','Safeguarding Adults (Level 1)','Basic Life 
Support','Chaperoning','Consent','On Call Training','Safeguarding Children 
(Level 3)','Infection Control (Clinical)',
'Moving and Handling (Clinical)')
group by l.[employee id],
l.firstname,
l.surname,
l.[job role],
l.manager,
t.module,
l.location,
m.[Frequency of training],
m.mandatory

Just note that you would need to include the ORDER BY in the query against the view instead of within the view.