Conditional UNION in stored procedure

Question

Bonjour!

So, in a stored procedure I would like to do a conditional union decided by a parameter. How can I do that?

Here is my "doesn't work" procedure :

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[spp_GetAdressesList]

    @OnlyLinked   bit         = 1,    
    @ObligedId    int         = -1 
AS 
BEGIN

    SELECT 
       [ID_ADRESS]
      ,[ID_ENT]
      ,[VOI_ADRESS]
      ,[NUM_ADRESS]
      ,[BTE_ADRESS]
      ,[CP_ADRESS]
      ,[VIL_ADRESS]

    FROM [ADRESSES]
    WHERE  
    (

        (VIL_ADRESS != 'NC' AND VIL_ADRESS != '--')
        AND
        (@OnlyLinked = 0 OR ID_ENT is not null)

    )

    IF (@ObligedId != -1)
    BEGIN
        UNION
            SELECT 
               [ID_ADRESS]
              ,[ID_ENT]
              ,[VOI_ADRESS]
              ,[NUM_ADRESS]
              ,[BTE_ADRESS]
              ,[CP_ADRESS]
              ,[VIL_ADRESS]

            FROM [ADRESSES]
            WHERE  
            ID_ADRESS = @ObligedId
    END

END

So if @ObligedId est = a -1 I would like to doesn't have the UNION.

I made this with a dynamic varchar query, at the end I was executing the query with an exec. But it's apparently less efficient and you can make sql injection (It is for asp.net application) with dynamic queries. I decided to change all my stored procedures

It's not possible to do an union in a IF clause?

Thanks for all answers without exceptions..

Is `-1` a valid data value for `ADRESSES.ID_ADRESS` ? If it's not, you could just always do the `UNION` . Also, "Address" has two "d"s :) — AakashM, Jan 20 '11 at 10:58
"adresse" in French.. ok for doing union but I want to understand how can I do that if -1 is a valid ID — bAN, Jan 20 '11 at 11:01

score 32 · Accepted Answer · answered Jan 20 '11 at 11:29

Normally to do a case based union, you transform the pseudo

select 1 AS A
IF @b!=-1 then
    union all
    select 2 as B
END IF

into

select 1 AS A
    union all
    select 2 as B WHERE @b!=-1  -- the condition covers the entire select
             -- because it is a variable test, SQL Server does it first and
             -- aborts the entire part of the union if not true

For your query, that becomes

SELECT 
   [ID_ADRESS],[ID_ENT],[VOI_ADRESS],[NUM_ADRESS],[BTE_ADRESS]
  ,[CP_ADRESS],[VIL_ADRESS]
FROM [ADRESSES]
WHERE  
(
    (VIL_ADRESS != 'NC' AND VIL_ADRESS != '--')
    AND
    (@OnlyLinked = 0 OR ID_ENT is not null)
)
    UNION
        SELECT 
           [ID_ADRESS],[ID_ENT],[VOI_ADRESS],[NUM_ADRESS],[BTE_ADRESS]
          ,[CP_ADRESS],[VIL_ADRESS]
        FROM [ADRESSES]
        WHERE  
        ID_ADRESS = @ObligedId
        AND (@ObligedId != -1)

However, since in this specific query, the data is from the same table just different filters, you would OR the filters instead. Note: if you had used UNION ALL, it can not be reduced this way because of possible duplicates that UNION ALL preserves. For UNION (which removes duplicates anyway), the OR reduction works just fine

SELECT 
   [ID_ADRESS],[ID_ENT],[VOI_ADRESS],[NUM_ADRESS],[BTE_ADRESS]
  ,[CP_ADRESS],[VIL_ADRESS]
FROM [ADRESSES]
WHERE  
(
    (VIL_ADRESS != 'NC' AND VIL_ADRESS != '--')
    AND
    (@OnlyLinked = 0 OR ID_ENT is not null)
)
OR
(
    ID_ADRESS = @ObligedId
    AND (@ObligedId != -1)   -- include this
)

Worth flagging that this has it's own problems. I'm engineering out WHERE clauses based on comparing a parameter to a fixed value as these still contain overhead. I've found that SQL Server doesn't necessarialy "short-cut" queries, so in this instead it'll still process data based on "ID_ADDRESS = @ObligedId" regardless of the value of @ObligedId. This can add a fairly nasty performance overhead. — Chris J, Jan 20 '11 at 13:02
@Chris have a look here for a discussion on that: http://www.sommarskog.se/dyn-search-2008.html and http://www.sommarskog.se/dyn-search-2005.html#OR_ISNULL — RichardTheKiwi, Jan 20 '11 at 18:33

score 6 · Answer 2 · answered Jan 20 '11 at 11:05

You could use a where clause to choose either end of the union:

select col1, col2 from TableA where @Param = 1
UNION ALL
select col1, col2 from TableB where @Param = 2

In your example, you could omit the IF statement entirely, since no address will have an ID_ADDRESS of -1.

score 2 · Answer 3 · answered Jan 20 '11 at 11:14

Couldn't you just rewrite your query like this:

SELECT 
   [ID_ADRESS]
  ,[ID_ENT]
  ,[VOI_ADRESS]
  ,[NUM_ADRESS]
  ,[BTE_ADRESS]
  ,[CP_ADRESS]
  ,[VIL_ADRESS]

FROM [ADRESSES]
WHERE  
(

    (VIL_ADRESS != 'NC' AND VIL_ADRESS != '--')
    AND
    (@OnlyLinked = 0 OR ID_ENT is not null)

)
Or ID_ADRESS = @ObligedId

If @obligedid is equal to -1 it won't find the Id and there for won't return the row. If it is valid id then it will return the row along with the rows returned in the first query.

score 0 · Answer 4 · answered Jan 20 '11 at 11:40

i'm curious would it not work to use one select and then use the two where statements but seperate them by an or like

 SELECT [ID_ADRESS],
       [ID_ENT],
       [VOI_ADRESS],
       [NUM_ADRESS],
       [BTE_ADRESS],
       [CP_ADRESS],
       [VIL_ADRESS]      
FROM [ADRESSES]     
WHERE       
(          
  (VIL_ADRESS != 'NC' AND VIL_ADRESS != '--')         
AND         
  (@OnlyLinked = 0 OR ID_ENT is not null)      
) OR ID_ADRESS =-1

Conditional UNION in stored procedure

4 Answers4