How come the generated code for a retrieval sproc returns a DataSet and not a DataTable?

Here is the sql which returns a single result set:

/*
    spAppInformationForDistrictUser
    Creation Date: 08/18/2004
    Programmer: John Puttman
    Description:
        Returns a District users access to all apps or a single app
-------------------------------------------------------------------------
    Modifications
        (List Most recent modification first)

    Date:
    Programmer
    Description:

*/

ALTER PROCEDURE spAppInformationForDistrictUser
    @parDistrictUserIdent INT, 
    @parApplicationID varchar(10)
AS


IF @parApplicationID IS NULL
    BEGIN --Return All Applications

    SELECT d.DepartmentID, d.DepartmentAbbr, dus.TeacherID, st.StaffTypeId [UserCategory], a.ApplicationId [ApplicationName], 'N' [ApplicationRecord], 
         '<Main>' [SubItem], act.AccessTypeId [AccessType], act.Priority
    FROM DistrictUser du
        JOIN DeptSetUser dsu 
            ON du.DistrictUserIdent = dsu.DistrictUserIdent
        JOIN DeptSetDept dsd
            ON dsu.DeptSetIdent = dsd.DeptSetIdent
        JOIN Department d
            ON dsd.DepartmentIdent = d.DepartmentIdent
        JOIN DeptSetUserJob dsuj
            ON dsu.DeptSetUserIdent = dsuj.DeptSetUserIdent
        JOIN JobApp ja
            ON dsuj.JobIdent = ja.JobIdent
        JOIN Application a
            ON ja.ApplicationIdent = a.ApplicationIdent
        JOIN AccessType act
            ON ja.AccessTypeIdent = act.AccessTypeIdent
        LEFT JOIN DistUserStaff dus
            ON du.DistrictUserIdent = dus.DistrictUserIdent AND d.DepartmentIdent = dus.DepartmentIdent
        LEFT JOIN StaffType st
            ON dus.StaffTypeIdent = st.StaffTypeIdent       
    WHERE du.DistrictUserIdent = @parDistrictUserIdent
        
    UNION ALL
    
    SELECT d.DepartmentID, d.DepartmentAbbr, dus.TeacherID, st.StaffTypeId [UserCategory], a.ApplicationId [ApplicationName], 'Y' [ApplicationRecord], 
         '<Main>' [SubItem], act.AccessTypeId [AccessType], act.Priority
    FROM DistrictUser du
        JOIN DeptSetUser dsu 
            ON du.DistrictUserIdent = dsu.DistrictUserIdent
        JOIN DeptSetDept dsd
            ON dsu.DeptSetIdent = dsd.DeptSetIdent
        JOIN Department d
            ON dsd.DepartmentIdent = d.DepartmentIdent
        JOIN DeptSetUserApp dsua
            ON dsu.DeptSetUserIdent = dsua.DeptSetUserIdent
        JOIN Application a
            ON dsua.ApplicationIdent = a.ApplicationIdent
        JOIN AccessType act
            ON dsua.AccessTypeIdent = act.AccessTypeIdent
        JOIN    (
            SELECT d.DepartmentID, a.ApplicationId, MAX(act.Priority) [MAXPriority]
            FROM DistrictUser du
                JOIN DeptSetUser dsu 
                    ON du.DistrictUserIdent = dsu.DistrictUserIdent
                JOIN DeptSetDept dsd
                    ON dsu.DeptSetIdent = dsd.DeptSetIdent
                JOIN Department d
                    ON dsd.DepartmentIdent = d.DepartmentIdent
                JOIN DeptSetUserApp dsua
                    ON dsu.DeptSetUserIdent = dsua.DeptSetUserIdent
                JOIN Application a
                    ON dsua.ApplicationIdent = a.ApplicationIdent
                JOIN AccessType act
                    ON dsua.AccessTypeIdent = act.AccessTypeIdent       
            WHERE du.DistrictUserIdent = @parDistrictUserIdent
            GROUP BY d.DepartmentID, a.ApplicationId
            ) maxofa
            ON maxofa.DepartmentID = d.DepartmentID AND maxofa.ApplicationID = a.ApplicationID AND maxofa.MAXPriority = act.Priority 
        LEFT JOIN DistUserStaff dus
            ON du.DistrictUserIdent = dus.DistrictUserIdent AND d.DepartmentIdent = dus.DepartmentIdent
        LEFT JOIN StaffType st
            ON dus.StaffTypeIdent = st.StaffTypeIdent
    WHERE du.DistrictUserIdent = @parDistrictUserIdent

    END --Return All Applications
ELSE
    BEGIN --Return records for input application only
    
        SELECT d.DepartmentID, d.DepartmentAbbr, dus.TeacherID, st.StaffTypeId [UserCategory], a.ApplicationId [ApplicationName], 'N' [ApplicationRecord], 
         '<Main>' [SubItem], act.AccessTypeId [AccessType], act.Priority
    FROM DistrictUser du
        JOIN DeptSetUser dsu 
            ON du.DistrictUserIdent = dsu.DistrictUserIdent
        JOIN DeptSetDept dsd
            ON dsu.DeptSetIdent = dsd.DeptSetIdent
        JOIN Department d
            ON dsd.DepartmentIdent = d.DepartmentIdent
        JOIN DeptSetUserJob dsuj
            ON dsu.DeptSetUserIdent = dsuj.DeptSetUserIdent
        JOIN JobApp ja
            ON dsuj.JobIdent = ja.JobIdent
        JOIN Application a
            ON ja.ApplicationIdent = a.ApplicationIdent
        JOIN AccessType act
            ON ja.AccessTypeIdent = act.AccessTypeIdent
        LEFT JOIN DistUserStaff dus
            ON du.DistrictUserIdent = dus.DistrictUserIdent AND d.DepartmentIdent = dus.DepartmentIdent
        LEFT JOIN StaffType st
            ON dus.StaffTypeIdent = st.StaffTypeIdent       
    WHERE du.DistrictUserIdent = @parDistrictUserIdent
        AND a.ApplicationID LIKE (@parApplicationID)
        
    UNION ALL
    
    SELECT d.DepartmentID, d.DepartmentAbbr, dus.TeacherID, st.StaffTypeId [UserCategory], a.ApplicationId [ApplicationName], 'Y' [ApplicationRecord], 
         '<Main>' [SubItem], act.AccessTypeId [AccessType], act.Priority
    FROM DistrictUser du
        JOIN DeptSetUser dsu 
            ON du.DistrictUserIdent = dsu.DistrictUserIdent
        JOIN DeptSetDept dsd
            ON dsu.DeptSetIdent = dsd.DeptSetIdent
        JOIN Department d
            ON dsd.DepartmentIdent = d.DepartmentIdent
        JOIN DeptSetUserApp dsua
            ON dsu.DeptSetUserIdent = dsua.DeptSetUserIdent
        JOIN Application a
            ON dsua.ApplicationIdent = a.ApplicationIdent
        JOIN AccessType act
            ON dsua.AccessTypeIdent = act.AccessTypeIdent
        JOIN    (
            SELECT d.DepartmentID, a.ApplicationId, MAX(act.Priority) [MAXPriority]
            FROM DistrictUser du
                JOIN DeptSetUser dsu 
                    ON du.DistrictUserIdent = dsu.DistrictUserIdent
                JOIN DeptSetDept dsd
                    ON dsu.DeptSetIdent = dsd.DeptSetIdent
                JOIN Department d
                    ON dsd.DepartmentIdent = d.DepartmentIdent
                JOIN DeptSetUserApp dsua
                    ON dsu.DeptSetUserIdent = dsua.DeptSetUserIdent
                JOIN Application a
                    ON dsua.ApplicationIdent = a.ApplicationIdent
                JOIN AccessType act
                    ON dsua.AccessTypeIdent = act.AccessTypeIdent       
            WHERE du.DistrictUserIdent = @parDistrictUserIdent
            GROUP BY d.DepartmentID, a.ApplicationId
            ) maxofa
            ON maxofa.DepartmentID = d.DepartmentID AND maxofa.ApplicationID = a.ApplicationID AND maxofa.MAXPriority = act.Priority 
        LEFT JOIN DistUserStaff dus
            ON du.DistrictUserIdent = dus.DistrictUserIdent AND d.DepartmentIdent = dus.DepartmentIdent
        LEFT JOIN StaffType st
            ON dus.StaffTypeIdent = st.StaffTypeIdent
    WHERE du.DistrictUserIdent = @parDistrictUserIdent
        AND a.ApplicationID LIKE (@parApplicationID)
    
    END --Return records for input application only

Is it the Union that confuses it?

Thanks,

Fishy

I think the IF. I use SqlServer's way of determining resultsets, so if it fails, it's sqlserver's fault, it apparently returns 2 for the amount of resultsets, which results in a dataset. If you want a datatable, change the amount of resultsets for this procedure in the catalog explorer to 1.

Thanks

I think I can restructure it to eliminate the IF. If not, then I will do what you suggest.

btw, not that I'm going to do it but, is it possible to restructure it to use entities? Or, if I created a view filtered on the two parameters, would that work? Would it be less effecient?

Thanks.

Fishy

Well, you combine fields from several tables in the resultset, so it will be better to use a view. To make this efficient, get rid of the IF. Your IF statement will make SqlServer compile the T-SQL with every execution, i.e.: it will not cache (or be able to re-use) the execution plan (SqlServer compiles every procedure prior to execution, unless there is a cached execution plan available in memory (these are never stored)). So split it up in 2 views