Post reply

If Else logic

  • January 14, 2015 at 9:55 am

    #397870

    Hi,

    Sample data

    Declare @IsSalaryApproved bit = 0;

    declare @t table (Id int, CompanyID int,IsSalaryApproved bit)

    insert into @t

    select data.Id, data.CompanyID,data.IsSalaryApproved from (

    select 1 as Id, 100 as CompanyID, 1 as IsSalaryApproved union all

    select 2 as Id, 200 as CompanyID, 0 as IsSalaryApproved union all

    select 3 as Id,300 as CompanyID, 0 as IsSalaryApproved union all

    select 4 as Id, 400 as CompanyID, 1 as IsSalaryApproved union all

    select 6 as ID, 500 as CompanyID, 1 as IsSalaryApproved ) data

    --Requirement : If i pass 0 then it has to get the rows corresonding to (IsSalaryApproved = 0) else it has to bring all the records.

    Sample Try:

    If @IsSalaryApproved = 0

    BEGIN

    select * from @t where IsSalaryApproved = 0

    END

    ELSE

    BEGIN

    select * from @t

    END

    Please let me know is there any better way to achieve this by avoiding If else.

    thanks

  • January 14, 2015 at 10:09 am

    #1770169

    Use the variabale in the WHERE clause.

    select *

    from @t

    where IsSalaryApproved = @IsSalaryApproved

    EDIT: Sorry, I need more coffee to read carefully.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • January 14, 2015 at 10:15 am

    #1770171

    --Requirement : If i pass 0 then it has to get the rows corresonding to (IsSalaryApproved = 0) else it has to bring all the records.

    You can do:

    select *

    from @t

    where IsSalaryApproved = @IsSalaryApproved OR @IsSalaryApproved != 0

    as for the best way, read this:

    http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • January 14, 2015 at 10:41 am

    #1770179

    Hi Luiz,

    thanks for your reply

    If i use the variable alone in the where clause, below condition will not work,

    if i pass value other then 0, i need to pull all records which will not happen.

    Hi Eugene ,

    Thanks, your solution helped me to proceed.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply