x

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 
more ▼

asked Oct 22, 2009 at 08:42 AM in Default

Mark Veitch gravatar image

Mark Veitch
23 1 1 1

(comments are locked)
10|1200 characters needed characters left

4 answers: sort voted first

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

more ▼

answered Oct 22, 2009 at 10:56 AM

Melvyn Harbour 1 gravatar image

Melvyn Harbour 1 ♦♦
1.4k 18 20 22

(comments are locked)
10|1200 characters needed characters left

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

more ▼

answered Oct 22, 2009 at 12:20 PM

Mark Veitch gravatar image

Mark Veitch
23 1 1 1

Suggest you mark this as the answer - I think it's better than the dynamic SQL.
Oct 22, 2009 at 01:11 PM Melvyn Harbour 1 ♦♦
(comments are locked)
10|1200 characters needed characters left

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 
more ▼

answered Oct 22, 2009 at 08:53 AM

Melvyn Harbour 1 gravatar image

Melvyn Harbour 1 ♦♦
1.4k 18 20 22

I see what you are saying and yes, this would be a useful solution under normal circumstances. However, we have nested SELECTs and one of the possible excluded fields is on the outer table. Given the size of the SP, doubling the size of it is not really an option. It would work in some of the nested SELECT though.
Oct 22, 2009 at 09:00 AM Mark Veitch
Are you trying to remove the whole column from the select, or can you cope with it just being null if the switch is not set?
Oct 22, 2009 at 09:20 AM Melvyn Harbour 1 ♦♦
Need to remove the whole column I'm afraid otherwise I would have used a CASE statement to set the field to NULL. I could build some of the nested SELECTS dynamically (conditionally) in a similar way to the method you mentioned but I'd still have the same issue with the outermost table.
Oct 22, 2009 at 10:45 AM Mark Veitch
(comments are locked)
10|1200 characters needed characters left

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

more ▼

answered Oct 22, 2009 at 12:36 PM

sp_lock gravatar image

sp_lock
9.2k 25 28 31

Yup. Valid comment. I'll amend my answer.
Oct 22, 2009 at 01:10 PM Melvyn Harbour 1 ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1948
x986
x413

asked: Oct 22, 2009 at 08:42 AM

Seen: 3717 times

Last Updated: Oct 22, 2009 at 08:55 AM