Conditional UNION in stored procedure

Question

Bonjour!

So, in a stored procedure, I would like to make a conditional join defined by a parameter. How can i do this?

Here is my “not working” 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 for UNION not to have.

I did this with a dynamic varchar query, at the end I executed the query with exec. But this, apparently, is less efficient, and you can do SQL injection (it is for asp.net application) with dynamic queries. I decided to change all my stored procedures.

Cannot join in an IF clause?

Thanks for all the answers without exception ..

Answer 1

Usually for case-based aggregation you convert pseudo

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

in

 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 

At your request it will be

 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 particular query the data from the same table uses only different filters, you should use OR filters instead. Note: if you used UNION ALL, it cannot be reduced this way because of possible duplicates that UNION ALL saves. For UNION (which removes duplicates anyway), OR shorthand 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 ) 

Answer 2

Could you just rewrite your request as follows:

 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 -1, it will not find Id and the string will not return for it there. If this is a valid identifier, it will return a string along with the strings returned in the first request.

Answer 3

You can use the where clause to select the end of the join:

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

In your example, you can completely omit the IF , since the address will not have ID_ADDRESS -1.

Answer 4

I am curious if one choice will not work and then use two where statements, but separate them with

  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