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..

Answer 1

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
)