I'm working with Microsoft Reporting Services 2008. In our database, we have a small group of confidential students that need to be taken into account for several queries. If they are a confidential student, the database needs to essentially return nothing. Currently, we do something along the lines of:
select case when CONFIDENTIALITY_IND = 'Y' or :EmployeeClass = 'XA' then 'CONFIDENTIAL' else db.table.name end as name from db.student
This isn't a big deal for such a small query, but most of them return many fields (often 20+) and I'm not a fan of so many case statements for obvious reasons. Because of how Reporting Services work, I can't do a general replacement either, it must be done on each and every field.
Is there a more efficient method where I could replace every value in the row with 'Confidential' in a single case statement, or at least something more elegant?
Edit: To clarify, that there isn't just this one field in the select. I only wrote one for the example, but in production, some reports are looking at displaying a huge amount of columns. I'm trying to avoid making two comparisons on every column for reports that could return rather large sets of data.