How to create a "fake" row in a SELECT statement of a stored procedure

I have a stored procedure proc_Audit_GetAppEmployedRelative, the purpose of which is to return previous applicants relatives info. The following SELECT statement is from the procedure:

Select  a.RowID, 
        a.ApplicantRowID,
        a.RelativeName as SearsRel开发者_运维知识库Name,
                Case When a.RelativeName <> 'N/A' then 'Yes' End as RelY,
                Case When a.RelativeName = 'N/A' then 'Yes' End as RelN,
        a.Relationship as SearsRelRelation,
                a.RelativeJob as SearsRelJobType,
        a.RelativeLoc as SearsRelJobLoc
    From dbo.App_EmplRelative a(nolock)
    Where a.ApplicantRowID = @ApplicantRowID

I have a requirement to return RelN = Yes even if other fields are empty in the table App_EmplRelative. Basically applicant do not have any employed relative information. I just have to fake a row to get RelN = Yes on the form. Please let me know how do I make a fake row, also I still need the above select statement in the proc.

You can put the result set into a variable and then test if the result set is empty. If so, you can create a fake row by selecting the actual values. There are better ways, but this is what pops into my brain right now.

SELECT TOP 1 null, null, null, 'Yes', null, null, null 
FROM dbo.App_EmplRelative a (nolock) 

My preference would be to have a test for the @ApplicationRowId first and then query only when valid, or handle the null on the coding side. NOTE: In 2005, you can also code with .NET languages and handle this type of manipulation there.

Select a.RowID,
       a.ApplicantRowID,
       a.RelativeName as SearsRelName,
       Case
         When a.RelativeName <> 'N/A' then 'Yes'  End as RelY,
       Case
         When a.RelativeName = 'N/A'
            or a.ApplicantRowID is null then 'Yes' End as RelN,
       a.Relationship as SearsRelRelation,
       a.RelativeJob  as SearsRelJobType,
       a.RelativeLoc  as SearsRelJobLoc
From   (SELECT 1 AS C) T
       LEFT JOIN dbo.App_EmplRelative a(nolock)
         ON a.ApplicantRowID = @ApplicantRowID