How to return an empty set with named columns
I was writing a Sql Server stored procedure and wanted it to return an empty record set if an error occurred and I wanted the column headings to be the same as for the result set when there is no error. For example, when there is no error the stored procedure would output:
|
UserName |
UserID |
UserSex |
|
Joe |
1234 |
Male |
|
Jane |
5678 |
Female |
When an error occurred, the stored procedure would output a table with no records:
|
UserName |
UserID |
UserSex |
So, what is an easy way to get this empty set? The answer is a simple Where clause that will never be true (click here to see a case when 1=2 http://www.techiebreak.com/uncategorized/algebraic-proof-that-12/) :
SELECT NULL AS UserName, NULL AS UserID, NULL AS UserSex WHERE 1=2
And that’s it! Note that the fact that I used “NULL” for a value is not important because the value will never acutally show up.
[...] was writing a post at http://www.dbtechie.com/sql-server/how-to-return-an-empty-set-with-named-columns/ where I made the statement that “1=2” will never be true. This reminded me of my teaching [...]