CODEWITHSUNDEEP
sql-serverif-statement
gymcode
gymcode

Reputation: 4623

SQL Server : using one Select statement with 2 conditions

I have the following SQL query, and I would need it to run SELECT statement only once.

May I know what are the changes I should make?

EDIT: Need to change to use only one SELECT statement..

DECLARE
@lang varchar(2) ='en',
@hideInactiveCompany integer = 0

IF @hideInactiveCompany = 1
SELECT <all columns>
  FROM Config.BusinessUnit BU
  LEFT JOIN Config.Organization CO on BU.OrganizationId = CO.OrganizationId
  LEFT JOIN Config.ParentBusinessUnit PBU on BU.ParentBusinessUnitId = PBU.ParentBusinessUnitId 
  WHERE BU.IsActive = 1 --the additional condition if @hideInactiveCompany=1
  ORDER BY CASE WHEN @lang = 'cn' THEN BU.Lang END,
           CASE WHEN @lang = 'en' THEN BU.Lang END DESC, 
           EntityName
ELSE
SELECT <all columns>
  FROM Config.BusinessUnit BU
  LEFT JOIN Config.Organization CO on BU.OrganizationId = CO.OrganizationId
  LEFT JOIN Config.ParentBusinessUnit PBU on BU.ParentBusinessUnitId = PBU.ParentBusinessUnitId 
  ORDER BY CASE WHEN @lang = 'cn' THEN BU.Lang END,
           CASE WHEN @lang = 'en' THEN BU.Lang END DESC, 
           EntityName

If @hideInactiveCompany = 1, display output with BU.IsActive =1

If @hideInactiveCompany = 0, display output without BU.IsActive filter.

Upvotes: 1

Views: 103

Answers (4)

Squirrel
Squirrel

Reputation: 24763

If i do understand your requirement correctly, the entire query should have only one single SELECT clause

DECLARE
    @lang varchar(2) ='en',
    @hideInactiveCompany integer = 0

SELECT    <all columns>
FROM      Config.BusinessUnit BU
LEFT JOIN Config.Organization CO on BU.OrganizationId = CO.OrganizationId
LEFT JOIN Config.ParentBusinessUnit PBU on BU.ParentBusinessUnitId = PBU.ParentBusinessUnitId 
WHERE     (@hideInactiveCompany = 1 AND BU.IsActive = 1)
OR        (@hideInactiveCompany <> 1)
ORDER BY   CASE WHEN @lang = 'cn' THEN BU.Lang END,
           CASE WHEN @lang = 'en' THEN BU.Lang END DESC, 
           EntityName

Explanation on WHERE Basically there are only 2 conditions:-

  • Condition 1 : @hideInactiveCompany = 1 AND BU.IsActive = 1
  • Condition 2 : @hideInactiveCompany <> 1

Condition 1 is as what you specified. When variable @hideInactiveCompany is 1, IsActive must be 1

Condition 2 is when variable @hideInactiveCompany not equal to 1

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521053

We can try writing the following WHERE clause:

WHERE
    (hideInactiveCompany = 1  AND BU.IsActive IS NOT NULL) OR
    (hideInactiveCompany IS NULL AND BU.IsActive IS NULL) OR
    (hideInactiveCompany <> 1)

Note: The OP has since changed the question a few times after I answered. The latest answer appears to be this:

WHERE
    (hideInactiveCompany = 1 AND BU.IsActive = 1) OR
    hideInactiveCompany IS NULL OR hideInactiveCompany <> 1

Upvotes: 3

Vijunav Vastivch
Vijunav Vastivch

Reputation: 4191

Use IF ELSE condition:

ALTER PROCEDURE [Config].[usp_ListBusinessUnit] 
@lang VARCHAR(2) = NULL,
@hideInactiveCompany INTEGER
AS
BEGIN


IF @hideInactiveCompany=1
    SELECT <all columns>
    FROM Config.BusinessUnit BU
    LEFT JOIN Config.Organization CO ON BU.OrganizationId = CO.OrganizationId
    LEFT JOIN Config.ParentBusinessUnit PBU ON BU.ParentBusinessUnitId = 
PBU.ParentBusinessUnitId 
    WHERE BU.IsActive IS NOT NULL
    ORDER BY 
        CASE WHEN @lang = 'cn' THEN BU.Lang END,
        CASE WHEN @lang = 'en' THEN BU.Lang END DESC, 
        EntityName

ELSE

   SELECT <all columns>
    FROM Config.BusinessUnit BU
    LEFT JOIN Config.Organization CO ON BU.OrganizationId = CO.OrganizationId
    LEFT JOIN Config.ParentBusinessUnit PBU ON BU.ParentBusinessUnitId = 
PBU.ParentBusinessUnitId 
    WHERE BU.IsActive IS NULL
    ORDER BY 
    CASE WHEN @lang = 'cn' THEN BU.Lang END,
    CASE WHEN @lang = 'en' THEN BU.Lang END DESC, 
    EntityName

END

Upvotes: 0

Richard
Richard

Reputation: 108975

You cannot do such a "conditionally build my select" in SQL. You need to write the complete select query twice, one for each branch of the if.

(SQL is generally not a "composable" langauge, you cannot freely combine the elements how you want.)

You might be able to use case and other expressions to rebuild in a single query, or doing the common part of the query into a temporary table (or table valued variable) and then only the final part twice.

Upvotes: 0

Related Questions