Exclude fields from a SELECT statement using parameter switches

Question

question

Mark Veitch asked Oct 22, '09

Exclude fields from a SELECT statement using parameter switches

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

sql-server-2005 t-sql stored-procedures

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2009-10-22T10:56:08Z

Melvyn Harbour 1 answered Oct 22, '09

Dynamic SQL may be your only answer if it's complicated then. You could do something like:

DECLARE @fields VARCHAR(MAX)
DECLARE @sql VARCHAR(MAX)

SET @sql = ' FROM dbo.vw_A A
  JOIN dbo.vw_B ON A.Id = B.Id
  WHERE @A_switch = 1
  FOR XML PATH(''A''), TYPE'

SET @fields = 'SELECT A.Id, Code, Description'

IF @B_switch = 1
    SET @fields = @fields + ', B.Text'

sp_executesql @fields + @sql

Edited to use sp_executesql to ensure query plan reuse and ability to use parameterised query

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 2 · 2009-10-22T08:53:43Z

Melvyn Harbour 1 answered Oct 22, '09

You should just be able to use standard conditional logic, surely:

IF @B_switch = 1 BEGIN
  SELECT A.Id, Code, Description, B.Text
  FROM dbo.vw_A A
  JOIN dbo.vw_B ON A.Id = B.Id
  WHERE @A_switch = 1
  FOR XML PATH('A'), TYPE
END ELSE BEGIN
  SELECT A.Id, Code, Description
  FROM dbo.vw_A A
  JOIN dbo.vw_B ON A.Id = B.Id
  WHERE @A_switch = 1
  FOR XML PATH('A'), TYPE
END

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 3 · 2009-10-22T12:20:27Z

Mark Veitch answered Oct 22, '09

Found it.

Thanks anyway Melvyn as I've garnered some food for thought with your answers.

The solution is (as simple as) :

SELECT A.Id, Code, Description,

CASE WHEN @B_switch THEN B.Text END 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

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 4 · 2009-10-22T12:36:56Z

sp_lock answered Oct 22, '09

Using Melvyns way ,but instead of EXEC use sp_executesql. This allows you to also use parameterized statements and will reuse an execution plan.

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question