Here is a section of a dynamically built stored procedure in a shortened format. This is part of a large stored procedure that de-normalises a bunch of tables and returns all the data in an XML document. The dynamic part of this involves passing in switches as BIT that can be used to exclude tables from the returned document. In this section we may or may not require table A and this is successfully done using @A_Switch.
What happens when this table is returned is that we may or may not require the associated field 'Text' from table B. We have a switch passed in called @B_switch
My question is how do I exclude B.Text from the select?
I have been informed that I could use CASE but cannot see how to do this.
Any advice would be much appreciated.
SELECT A.Id, Code, Description, B.Text AS "B/Text" FROM dbo.vw_A A JOIN dbo.vw_B B ON A.Id = B.Id WHERE @A_switch = 1 FOR XML PATH('A'), TYPE