SQL: How to return all NULLs when no record found?

Question

I have a simple SELECT...

SELECT  TETmeetingID,
        DateID,
        WeekNo,
        TETID,
        TradeStaffID,
        EngStaffID,
        MathsStaffID,
        IoTStaffID,
        Others,
        TradeStaffPresent,
        EngStaffPresent,
        MathsStaffPresent,
        IoTStaffPresent,
        CurricTrade,
        CurricEnglish,
        CurricMaths,
        CurricIoT,
        CurricAlign,
        Notes,
        WeeksToGo,
        TotalWeeks,
        CreatedDate,
        CreatedBy,
        Complete,
        CompletedDate,
        CompletedBy
FROM tblTETMeeting
WHERE TETmeetingID = @SomeParameter

Works of course when the WHERE clause returns true.

But if it does not, I want to at least see ONE record with NULLs in every column.

How can I do this please? And I chose the above example to show the possible number of columns I could be using, so if there's a more generic query that can "determine" the columns and not have to hard-code them into the answer, that would be preferred.

Thanks

UPDATE

I've tried WITH TIES, but that also did not return a single record. Maybe I was doing it wrong. I've also tried the UNION NULL, NULL, etc trick, but that is messy when the number of columns could be undefined 'n'. The only way I can do this properly, but I am still forced to provide the correct number of NULLs is with a CTE type query.

WITH CTE (    SELECT  TETmeetingID,
            DateID,
            WeekNo,
            TETID,
            TradeStaffID,
            EngStaffID,
            MathsStaffID,
            IoTStaffID,
            Others,
            TradeStaffPresent,
            EngStaffPresent,
            MathsStaffPresent,
            IoTStaffPresent,
            CurricTrade,
            CurricEnglish,
            CurricMaths,
            CurricIoT,
            CurricAlign,
            Notes,
            WeeksToGo,
            TotalWeeks,
            CreatedDate,
            CreatedBy,
            Complete,
            CompletedDate,
            CompletedBy
    FROM tblTETMeeting
    WHERE TETmeetingID = @SomeParameter) AS tbl1

select * from CTE WHERE cte.TETmeetingID IS NOT NULL UNION SELECT NULL,NULL,NULL, etc

Answer 1

Remember UNION is the counterpart of logical OR:

SELECT TETmeetingID
  FROM tblTETMeeting
 WHERE TETmeetingID = @SomeParameter
UNION
SELECT NULL AS TETmeetingID  -- don't make me write them all out!
  FROM tblTETMeeting
 WHERE TETmeetingID <> @SomeParameter

Speaking of logic, remember in SQL a search condition (WHERE clause) can evaluate true, false and unknown due to SQL's confusing (and inconsistently applied) three-value logic. So if TETmeetingID or @SomeParameter can ever be null then you may need to add some code to handle this.

Answer 2

This does what you want:

SELECT C.*
FROM
(SELECT 1 As DummyColumn) As DummyTable
LEFT OUTER JOIN 
tblTETMeeting C
ON TETmeetingID = @SomeParameter

Answer 3

This should do.

The idea is to add a flag column and an extra row with NULL in all columns except the flag column. In this example, ConditionTrue is the flag column. 1 as true and 0 as false. This means that if the predicate in WHERE is true, the result will be all the rows returned with ConditionTrue of 1 plus a row of null with ConditioinTrue of 0. On the other hand, if nothing was returned, it'll be just the the row of null with ConditionTrue of 0. By putting the result into a temp table, you can then filter out unwanted rows and columns.

If the predicate is true, then there will always be 2 or more rows. So you can check @@ROWCOUNT to your advantage. 1 means no hit, bigger means hit.

Lastly, I don't want to type all 26 columns again, so I dropped the ConditionTrue column and used SELECT * as a shortcut. If you don't mind the flag column, leave it be, or you can move the ALTER statement before the IF block and filter out differently, like TETmeetingID IS NOT NULL.

IF OBJECT_ID('Tempdb..#tblTETMeeting','U') IS NOT NULL
    DROP TABLE Tempdb..#tblTETMeeting;

DECLARE @COUNTS INT;

SELECT  TETmeetingID,
        DateID,
        WeekNo,
        TETID,
        TradeStaffID,
        EngStaffID,
        MathsStaffID,
        IoTStaffID,
        Others,
        TradeStaffPresent,
        EngStaffPresent,
        MathsStaffPresent,
        IoTStaffPresent,
        CurricTrade,
        CurricEnglish,
        CurricMaths,
        CurricIoT,
        CurricAlign,
        Notes,
        WeeksToGo,
        TotalWeeks,
        CreatedDate,
        CreatedBy,
        Complete,
        CompletedDate,
        CompletedBy,
        1 AS ConditionTrue
INTO #tblTETMeeting
FROM tblTETMeeting
WHERE TETmeetingID = @SomeParameter
UNION ALL SELECT NULL, NULL, NULL, NULL, NULL, NULL,NULL, NULL,NULL, NULL, NULL, NULL, NULL, NULL,NULL, NULL,NULL, NULL, NULL, NULL, NULL, NULL,NULL, NULL,NULL,NULL, 0;

SET @COUNTS = @@ROWCOUNT; 

IF @COUNTS > 1 

    SELECT * FROM #tblTETMeeting WHERE ConditionTrue = 1;
ELSE
    BEGIN
        ALTER TABLE #tblTETMeeting 
            DROP COLUMN ConditionTrue;
        SELECT * FROM #tblTETMeeting;
    END;