Using a temp table in a view

Question

I would really like to create a view.

I know you can't use temp tables in MSSQL2005 views. Without rewriting the sql, is there anything obvious I've missed?

Backup plan is to use a stored proc.

Cheers

select * into #temp from vwIncidents 

SELECT     vwIncidents.incidentcode, employeecode, EMPOS.POS_L4_CDA as areaAtTimeOfIncident
into #temp1
FROM         vwIncidents 
INNER JOIN EMPOS ON vwIncidents.employeecode = EMPOS.DET_NUMBERA 
WHERE    EMPOS.POS_STARTC <  vwIncidents.incidentdate
AND      (EMPOS.POS_ENDD > vwIncidents.incidentdate OR EMPOS.POS_ENDD IS NULL)
order by incidentcode

select #temp.*, #temp1.areaAtTimeOfIncident from #temp
left outer join #temp1 on #temp.incidentcode = #temp1.incidentcode 
and #temp.employeecode = #temp1.employeecode
order by incidentcode

Answer 1

You could use a CTE:

WITH cteIncidents (incidentcode, employeecode, areaAtTimeOfIncident)
AS
(
SELECT 
    vwIncidents.incidentcode, employeecode, EMPOS.POS_L4_CDA as areaAtTimeOfIncident 
FROM
    vwIncidents  
INNER JOIN EMPOS ON vwIncidents.employeecode = EMPOS.DET_NUMBERA  
WHERE    EMPOS.POS_STARTC <  vwIncidents.incidentdate 
AND      (EMPOS.POS_ENDD > vwIncidents.incidentdate OR EMPOS.POS_ENDD IS NULL) 
)

SELECT 
    incidentcode, employeecode, areaAtTimeOfIncident
FROM 
    cteIncidents 
left outer join vwIncidents on vwIncidents.incidentcode = cteIncidents.incidentcode  
and vwIncidents.employeecode = cteIncidents.employeecode 
ORDER BY
    incidentcode 

(Might need to change the join to a right, but you get the idea...)

Answer 2

Hav you tried rewriting this without the use of temp tables?

Something like

select  temp.*, 
        temp1.areaAtTimeOfIncident 
from    (
            select * 
            from vwIncidents
        ) temp  left outer join 
        (
            SELECT  vwIncidents.incidentcode, 
                    employeecode, 
                    EMPOS.POS_L4_CDA as areaAtTimeOfIncident 
            FROM    vwIncidents  INNER JOIN 
                    EMPOS   ON vwIncidents.employeecode = EMPOS.DET_NUMBERA  
            WHERE   EMPOS.POS_STARTC <  vwIncidents.incidentdate 
            AND     (   
                        EMPOS.POS_ENDD > vwIncidents.incidentdate 
                        OR EMPOS.POS_ENDD IS NULL
                    )
        ) temp1     on  temp.incidentcode = temp1.incidentcode  
                and temp.employeecode = temp1.employeecode 
order by incidentcode