Return Results Even If CTE Doesn't Resolve

Question

I have a stored procedure on my SQL Server that consolidates a range of fields for use in SSRS for Report Builder. The procedure is fed a FileId and then works its logic. It works as intended until the File can't find or reference the Solicitor and Arresting Officer fields.

I need this to return a result even if the fileId does not have an Arresting Officer or Solicitor associated. I'm sure its something simple. Basically if the CTE returns nothing from the query, I still need a default row.

 ALTER PROCEDURE [dbo].[GetRollCallData] 
    @Ids        VARCHAR(255),
    @LexiconId  INT,
    @UUID       UNIQUEIDENTIFIER,
    @ReadOnly   INT
 AS


 DECLARE @TableCode INT
 SET @TableCode = 58

 IF @Ids <> ''
    BEGIN
        EXEC InsertInSelectionCache @Ids, @UUID, @TableCode, 0
        IF @ReadOnly = 1
            WITH DOACTE AS(
            SELECT ROW_NUMBER() OVER(PARTITION BY [File].Id ORDER BY CustomRecordsetId DESC) AS RowNumber, [File].*, FileType2Lexicon.Label as FileTypeLabel, [People].DefaultPhone, [People].InvertedName, CustomField.Name as FieldLabel, CustomFieldValue.Value as FieldValue
                FROM FileType2Lexicon, SelectionCache, [People], [File]
                INNER JOIN [CustomRecordSet]
                ON [CustomRecordset].RecordId = [File].Id
                INNER JOIN CustomFieldValue
                ON  [CustomRecordset].Id = CustomFieldValue.CustomRecordsetId
                INNER JOIN [CustomField2Lexicon]
                ON CustomField2Lexicon.CustomFieldId = CustomFieldValue.CustomFieldId
                INNER JOIN [CustomField]
                ON CustomField.Id = CustomField2Lexicon.CustomFieldId
                WHERE   [File].Id = SelectionCache.RecordId
                AND SelectionCache.UUID = @UUID
                AND SelectionCache.TableCode = @TableCode -- this is the code for File table  
                AND     [File].Id <> 0 
                AND     [File].FileTypeId = FileType2Lexicon.FileTypeId 
                AND     FileType2Lexicon.LexiconId = @LexiconId
                AND     [File].ClientIdString = [People].ClientIdString
                AND     CustomFieldValue.Value <> ''),

            SolicitorCTE AS(

                SELECT [People].Name AS SolicitorName, [File].Id
                FROM SelectionCache, [File]
                INNER JOIN [People2File]
                ON [People2File].FileId = [File].Id
                INNER JOIN [Role2Lexicon]
                ON [Role2Lexicon].RoleId = [People2File].RoleId
                INNER JOIN [People]
                ON [People].Id = [People2File].PeopleId
                WHERE 
                [File].Id = SelectionCache.RecordId
                AND SelectionCache.UUID = @UUID
                AND SelectionCache.TableCode = @TableCode -- this is the code for File table  
                AND [File].Id <> 0
                AND [Role2Lexicon].Label = 'Solicitor'),

             ArrestingOfficerCTE AS(
                SELECT ROW_NUMBER() OVER(PARTITION BY [File].Id ORDER BY [People].InvertedName ASC) AS RowNumber, [People].Name AS ArrestingOfficerName, [People].CompanyName AS ArrestingOfficerCompany, [File].Id
                FROM SelectionCache, [File]
                INNER JOIN [People2File]
                ON [People2File].FileId = [File].Id
                INNER JOIN [Role2Lexicon]
                ON [Role2Lexicon].RoleId = [People2File].RoleId
                INNER JOIN [People]
                ON [People].Id = [People2File].PeopleId
                WHERE 
                [File].Id = SelectionCache.RecordId
                AND SelectionCache.UUID = @UUID
                AND SelectionCache.TableCode = @TableCode -- this is the code for File table  
                AND [File].Id <> 0
                AND [Role2Lexicon].Label = 'Arresting Officer'),

            PivotCTE AS(
                SELECT *
                FROM
                (Select Id, FieldLabel, FieldValue FROM DOACTE) AS Source
                PIVOT(
                MAX(FieldValue) FOR FieldLabel IN ([Date_Arrest], [Graphic_Client], [Ticket_1], [Ticket_2], [Ticket_3], [Ticket_4], [Ticket_5], [Charge_1], [Charge_2], [Charge_3], [Charge_4], [Charge_5])) as Pvt
                )

            SELECT DOACTE.*, COALESCE(ArrestingOfficerCTE.ArrestingOfficerCompany, 'NULL')AS ArrestingOfficerCompany, COALESCE(ArrestingOfficerCTE.ArrestingOfficerName, 'NULL') AS ArrestingOfficerName, SolicitorCTE.SolicitorName, PivotCTE.[Date_Arrest], dbo.GetImagebyId(PivotCTE.[Graphic_Client]) as Photo, PivotCTE.[Ticket_1], PivotCTE.[Ticket_2], PivotCTE.[Ticket_3], PivotCTE.[Ticket_4], PivotCTE.[Ticket_5], PivotCTE.[Charge_1], PivotCTE.[Charge_2], PivotCTE.[Charge_3], PivotCTE.[Charge_4], PivotCTE.[Charge_5]
            FROM DOACTE
            INNER JOIN 
            PivotCTE
            ON DOACTE.Id = PivotCTE.Id
            INNER JOIN
            SolicitorCTE
            ON DOACTE.Id = SolicitorCTE.Id
            INNER JOIN
            ArrestingOfficerCTE
            ON DOACTE.Id = ArrestingOfficerCTE.Id
            WHERE DOACTE.RowNumber = 1
            AND ArrestingOfficerCTE.RowNumber = 1

        ELSE

        DELETE SelectionCache 
            WHERE UUID = @UUID
            AND   TableCode = @TableCode
    END

Answer 1

This is pretty much liebs19's answer, so I upvoted his since he got there first. I think the answer comes down to LEFT JOINing where you're INNER. Since I rewrote your query so I could actually understand what was going on and I made some changes you may or may not want, so I'll leave it here in case it's useful.

Mine is more lines than yours partially because I format differently, partially because I'm using a temp table to do once what you do around 4 times. Thing about CTE's is that when you stack them like this you're likely to get some really gnarly performance. The CTE's act basically like an on-the-fly view, and their SQL pretty much gets repeated everywhere. I'm taking your limiting [File] and moving it into a temp table. Then I'm pivoting it into another one (since this further limits things based on RowNumber being 1. Then I just use this with the 2 CTE's at the end and into your final result.

Lastly, SELECT * ... is evil. I only use it when I'm poking around querying for something. Putting it into a sproc like this will cause you issues as the schema or CTE's or so on change. When you're making a sproc, name every column, it'll bite you much less in the future.

Here it is, feel free to ignore if you don't find it useful:

ALTER PROCEDURE [dbo].[GetRollCallData] 
    @Ids        VARCHAR(255),
    @LexiconId  INT,
    @UUID       UNIQUEIDENTIFIER,
    @ReadOnly   INT
AS
BEGIN
    DECLARE @TableCode INT = 58;

    IF @Ids <> ''
    BEGIN
        EXEC InsertInSelectionCache @Ids, @UUID, @TableCode, 0

        IF @ReadOnly = 1
        BEGIN
            SELECT
                -- Add any other fully qualified columns. * is evil for sprocs. Only good for poking around in your own queries.
                AllFiles.Id
            INTO #SelectionFile
            FROM 
                [File] AllFiles
                INNER JOIN SelectionCache Cache
                    ON Cache.RecordId = AllFiles.Id
                    AND Cache.UUID = @UUID
                    AND Cache.TableCode = @TableCode -- this is the code for File table 
            WHERE
                AllFiles.Id <> 0

            ;WITH DOACTE AS
            (
                SELECT 
                    ROW_NUMBER() OVER(PARTITION BY SelectionFile.Id ORDER BY CustomRecordsetId DESC) AS RowNumber
                    , SelectionFile.Id AS FileId
                    -- [Replace with any other columns that you selected from [File] at the top.]
                    , Lexicon.Label AS FileTypeLabel
                    , [People].DefaultPhone
                    , [People].InvertedName
                    , CustomField.Name AS FieldLabel
                    , CustomFieldValue.Value AS FieldValue
                FROM 
                    #SelectionFile SelectionFile
                    INNER JOIN FileType2Lexicon Lexicon
                        ON Lexicon.FileTypeId = SelectionFile.FileTypeId
                        AND Lexicon.LexiconId = @LexiconId
                    INNER JOIN [People]
                        ON [People].ClientIdString = SelectionFile.ClientIdString
                    INNER JOIN [CustomRecordSet]
                        ON [CustomRecordset].RecordId = SelectionFile.Id
                    INNER JOIN CustomFieldValue
                        ON [CustomRecordset].Id = CustomFieldValue.CustomRecordsetId
                        AND CustomFieldValue.Value <> ''
                    INNER JOIN [CustomField2Lexicon]
                        ON CustomField2Lexicon.CustomFieldId = CustomFieldValue.CustomFieldId
                    INNER JOIN [CustomField]
                        ON CustomField.Id = CustomField2Lexicon.CustomFieldId
            )
            SELECT 
                Pvt.FileId
                -- [Replace with any other columns that you selected from DOACTE you want to propagate to the end.]
                , Pvt.[Date_Arrest]
                , Pvt.[Graphic_Client]
                , Pvt.[Ticket_1]
                , Pvt.[Ticket_2]
                , Pvt.[Ticket_3]
                , Pvt.[Ticket_4]
                , Pvt.[Ticket_5]
                , Pvt.[Charge_1]
                , Pvt.[Charge_2]
                , Pvt.[Charge_3]
                , Pvt.[Charge_4]
                , Pvt.[Charge_5]
            INTO #PivotedFile
            FROM
            (
                SELECT 
                    FileId
                    -- [Replace with any other columns that you selected from DOACTE you want to propagate to the end.]
                    , FieldLabel
                    , FieldValue 
                    , FieldLabel AS PivotFieldLabel
                    , FieldValue AS PivotFieldValue
                FROM 
                    DOACTE
                WHERE 
                    RowNumber = 1
            ) AS Source
            PIVOT
            (
                MAX(PivotFieldValue) 
                FOR PivotFieldLabel 
                IN 
                (
                    [Date_Arrest]
                    , [Graphic_Client]
                    , [Ticket_1]
                    , [Ticket_2]
                    , [Ticket_3]
                    , [Ticket_4]
                    , [Ticket_5]
                    , [Charge_1]
                    , [Charge_2]
                    , [Charge_3]
                    , [Charge_4]
                    , [Charge_5]
                )
            ) as Pvt

            ;WITH SolicitorCTE AS
            (
                SELECT 
                    [People].Name AS SolicitorName
                    , SelectedFiles.FileId
                FROM 
                    #PivotedFile SelectedFiles
                    INNER JOIN [People2File]
                        ON [People2File].FileId = SelectedFiles.FileId
                    INNER JOIN [Role2Lexicon]
                        ON [Role2Lexicon].RoleId = [People2File].RoleId
                        AND [Role2Lexicon].Label = 'Solicitor'
                    INNER JOIN [People]
                        ON [People].Id = [People2File].PeopleId
            )
            , ArrestingOfficerCTE AS
            (
                SELECT 
                    ROW_NUMBER() OVER(PARTITION BY SelectionFile.Id ORDER BY [People].InvertedName ASC) AS RowNumber
                    , [People].Name AS ArrestingOfficerName
                    , [People].CompanyName AS ArrestingOfficerCompany
                    , SelectedFiles.FileId
                FROM 
                    #PivotedFile SelectedFiles
                    INNER JOIN [People2File]
                        ON [People2File].FileId = SelectedFiles.FileId
                    INNER JOIN [Role2Lexicon]
                        ON [Role2Lexicon].RoleId = [People2File].RoleId
                        AND [Role2Lexicon].Label = 'Arresting Officer'
                    INNER JOIN [People]
                        ON [People].Id = [People2File].PeopleId
            )
            SELECT 
                SelectedFiles.Id
                -- [Replace with any other columns that you selected from DOACTE you want to propagate to the end.]
                , COALESCE(ArrestingOfficerCTE.ArrestingOfficerCompany, 'NULL') AS ArrestingOfficerCompany
                , COALESCE(ArrestingOfficerCTE.ArrestingOfficerName, 'NULL') AS ArrestingOfficerName
                , SolicitorCTE.SolicitorName
                , SelectedFiles.[Date_Arrest]
                , dbo.GetImagebyId(SelectedFiles.[Graphic_Client]) as Photo
                , SelectedFiles.[Ticket_1]
                , SelectedFiles.[Ticket_2]
                , SelectedFiles.[Ticket_3]
                , SelectedFiles.[Ticket_4]
                , SelectedFiles.[Ticket_5]
                , SelectedFiles.[Charge_1]
                , SelectedFiles.[Charge_2]
                , SelectedFiles.[Charge_3]
                , SelectedFiles.[Charge_4]
                , SelectedFiles.[Charge_5]
            FROM #PivotedFile SelectedFiles
                LEFT JOIN SolicitorCTE
                    ON SelectedFiles.FileId = SolicitorCTE.FileId
                LEFT JOIN ArrestingOfficerCTE
                    ON SelectedFiles.FileId = ArrestingOfficerCTE.FileId
                    AND ArrestingOfficerCTE.RowNumber = 1

        DROP TABLE #SelectionFile
        DROP TABLE #PivotedFile
        END
    ELSE
        DELETE SelectionCache 
        WHERE 
            UUID = @UUID
            AND TableCode = @TableCode
    END
END

(If you do use it, read the -- [Replace...] comments.

Answer 2

You can left join for optional results. I also needed to add a null check for the Arresting officer to the where caluse so that this didn't exclude records despite the left join.

SELECT DOACTE.*, COALESCE(ArrestingOfficerCTE.ArrestingOfficerCompany, 'NULL')AS ArrestingOfficerCompany, COALESCE(ArrestingOfficerCTE.ArrestingOfficerName, 'NULL') AS ArrestingOfficerName, SolicitorCTE.SolicitorName, PivotCTE.[Date_Arrest], dbo.GetImagebyId(PivotCTE.[Graphic_Client]) as Photo, PivotCTE.[Ticket_1], PivotCTE.[Ticket_2], PivotCTE.[Ticket_3], PivotCTE.[Ticket_4], PivotCTE.[Ticket_5], PivotCTE.[Charge_1], PivotCTE.[Charge_2], PivotCTE.[Charge_3], PivotCTE.[Charge_4], PivotCTE.[Charge_5]
            FROM DOACTE
            INNER JOIN 
            PivotCTE
            ON DOACTE.Id = PivotCTE.Id
            LEFT JOIN
            SolicitorCTE
            ON DOACTE.Id = SolicitorCTE.Id
            LEFT JOIN
            ArrestingOfficerCTE
            ON DOACTE.Id = ArrestingOfficerCTE.Id
            WHERE DOACTE.RowNumber = 1
            AND ( ArrestingOfficerCTE.RowNumber = 1 or ArrestingOfficerCTE.RowNumber is null )

Answer 3

Can you try:

SELECT DOACTE.*, COALESCE(ArrestingOfficerCTE.ArrestingOfficerCompany, 'NULL')AS ArrestingOfficerCompany, COALESCE(ArrestingOfficerCTE.ArrestingOfficerName, 'NULL') AS ArrestingOfficerName, SolicitorCTE.SolicitorName, PivotCTE.[Date_Arrest], dbo.GetImagebyId(PivotCTE.[Graphic_Client]) as Photo, PivotCTE.[Ticket_1], PivotCTE.[Ticket_2], PivotCTE.[Ticket_3], PivotCTE.[Ticket_4], PivotCTE.[Ticket_5], PivotCTE.[Charge_1], PivotCTE.[Charge_2], PivotCTE.[Charge_3], PivotCTE.[Charge_4], PivotCTE.[Charge_5]
into #Results
            FROM DOACTE
            INNER JOIN 
            PivotCTE
            ON DOACTE.Id = PivotCTE.Id
            INNER JOIN
            SolicitorCTE
            ON DOACTE.Id = SolicitorCTE.Id
            INNER JOIN
            ArrestingOfficerCTE
            ON DOACTE.Id = ArrestingOfficerCTE.Id
            WHERE DOACTE.RowNumber = 1
            AND ArrestingOfficerCTE.RowNumber = 1

if ((select cout(*) from #Results) = 0)
begin

insert into #Results
      select
           .......your default values......

end

select * from #Results

drop table #Results