IF Statement Syntax

9 Dec 2005 7:41 PM

Sherwood

Greetings,

I am having problems with the IF statement below and was wondering what I am
doing wrong? Can you not assign the results of an IF statement to a
variable? I am getting a syntax error when I try and run this code.

MyVar = if (b.Branch_No = 'MCORP')
              Begin
                  Convert(varchar(15),month(dateadd(day,30,ebm.Hire_Date)) +
'/' + Convert(varchar(15),1) + '/'
+Convert(varchar(15),year(dateadd(Month,1,dateadd(day,30,ebm.Hire_Date))))
              End
              Else
              Begin

Convert(varchar(15),month(dateadd(day,30,ebm.Hire_Date)) + '/' +
Convert(varchar(15),1) + '/'
+Convert(varchar(15),year(dateadd(Month,1,dateadd(day,30,ebm.Hire_Date))))
              End

[Note: The "Convert" statements work separately since I have already tested
them in CASE statement].

Thanks in advance!

--
Sherwood

9 Dec 2005 7:53 PM

Aaron Bertrand [SQL Server MVP]

IF is not used within a query, it is used for control of flow logic.

SELECT @MyVar = CASE b.Branch_No
WHEN 'MCORP' THEN ...big ugly char conversion #1...
ELSE ...big ugly char conversion #2...
END
FROM myTable b

<-- you refer to b.Branch_No, you need this to come from a table, otherwise
the conditional has no idea what you're talking about.
<-- you also need to ensure the query returns exactly one row, so unless
your table has only one row, you'll need a where clause. Or, you'll need to
provide better requirements.

Show quote

"Sherwood" <Sherw***@discussions.microsoft.com> wrote in message
news:C1E9FAB7-C2F5-43F6-A679-4A0AA9CA53DA@microsoft.com...
> Greetings,
>
> I am having problems with the IF statement below and was wondering what I
> am
> doing wrong? Can you not assign the results of an IF statement to a
> variable? I am getting a syntax error when I try and run this code.
>
> MyVar = if (b.Branch_No = 'MCORP')
>              Begin
>                  Convert(varchar(15),month(dateadd(day,30,ebm.Hire_Date))
> +
> '/' + Convert(varchar(15),1) + '/'
> +Convert(varchar(15),year(dateadd(Month,1,dateadd(day,30,ebm.Hire_Date))))
>              End
>              Else
>              Begin
>
> Convert(varchar(15),month(dateadd(day,30,ebm.Hire_Date)) + '/' +
> Convert(varchar(15),1) + '/'
> +Convert(varchar(15),year(dateadd(Month,1,dateadd(day,30,ebm.Hire_Date))))
>              End
>
> [Note: The "Convert" statements work separately since I have already
> tested
> them in CASE statement].
>
> Thanks in advance!
> --
> Sherwood

9 Dec 2005 8:15 PM

Sherwood

I guess I should have given you the entire SQL initially. Here it is. I
cannot get the IF statement below to work. I wasn't sure if you could assign
the results of an IF statement to a variable.

Thanks.

Select Distinct
    e.FIRST_NAME,
    e.LAST_NAME,
    tl.DESCR as Title,
    e.FULL_NAME,
    a.FULL_ADDRESS,
        Hire_Date_Plus_30_Days = Convert(varchar(15),ebm.Hire_Date + 30,101),
        Hire_Date_Plus_90_Days = Convert(varchar(15),ebm.Hire_Date + 90,101),
        Convert(varchar(15),ebm.HIRE_DATE,101) as Hire_Date,
        MyVar = if (b.Branch_No = 'MCORP')
                          Print 'If condition'
               else
                          Print 'Else condition',
    FRIENDLY_ADDRESS = a.CITY + ' , ' + s.CODE + ',' + a.POSTAL_CODE,
    a.ADDRESS,
    b.BRANCH_NO,
    bm.INSURANCE_ELIGIBILITY_DATE,
    p.NUMBER

from employee_master e
inner join employee_branch_mapping ebm on ebm.employee_id = e.[id] and
ebm.status_id = 1
left join title_lookup tl on tl.id = ebm.title_id
inner join branch_master b on b.[id] = ebm.branch_id
inner join address_master a ON a.[SOURCE_TABLE_ID] = e.[ID] AND
a.[SOURCE_TABLE_NAME] = 'EMPLOYEE_MASTER' AND a.ADDRESS_TYPE_ID = 3
inner join state_or_province_lookup s on s.ID = a.STATE_OR_PROVINCE_ID
left outer join benefits_master bm on bm.employee_id = e.[id]
left join phone_master p ON p.[SOURCE_TABLE_ID] = e.[ID] AND
p.[SOURCE_TABLE_NAME] = 'EMPLOYEE_MASTER' AND p.PHONE_TYPE_ID = 4
where
ebm.HIRE_DATE between convert(varchar(10),@StartDate,101) and
convert(varchar(10),@EndDate,101)
and b.branch_no like '%MCORP%'
order by e.LAST_NAME--

Show quote

"Aaron Bertrand [SQL Server MVP]" wrote:

> IF is not used within a query, it is used for control of flow logic.
>
> SELECT @MyVar = CASE b.Branch_No
>     WHEN 'MCORP' THEN ...big ugly char conversion #1...
>     ELSE ...big ugly char conversion #2...
> END
> FROM myTable b
>
> <-- you refer to b.Branch_No, you need this to come from a table, otherwise
> the conditional has no idea what you're talking about.
> <-- you also need to ensure the query returns exactly one row, so unless
> your table has only one row, you'll need a where clause. Or, you'll need to
> provide better requirements.
>
>
>
>
> "Sherwood" <Sherw***@discussions.microsoft.com> wrote in message
> news:C1E9FAB7-C2F5-43F6-A679-4A0AA9CA53DA@microsoft.com...
> > Greetings,
> >
> > I am having problems with the IF statement below and was wondering what I
> > am
> > doing wrong? Can you not assign the results of an IF statement to a
> > variable? I am getting a syntax error when I try and run this code.
> >
> > MyVar = if (b.Branch_No = 'MCORP')
> >              Begin
> >                  Convert(varchar(15),month(dateadd(day,30,ebm.Hire_Date))
> > +
> > '/' + Convert(varchar(15),1) + '/'
> > +Convert(varchar(15),year(dateadd(Month,1,dateadd(day,30,ebm.Hire_Date))))
> >              End
> >              Else
> >              Begin
> >
> > Convert(varchar(15),month(dateadd(day,30,ebm.Hire_Date)) + '/' +
> > Convert(varchar(15),1) + '/'
> > +Convert(varchar(15),year(dateadd(Month,1,dateadd(day,30,ebm.Hire_Date))))
> >              End
> >
> > [Note: The "Convert" statements work separately since I have already
> > tested
> > them in CASE statement].
> >
> > Thanks in advance!
> > --
> > Sherwood
>
>
>

9 Dec 2005 8:24 PM

Aaron Bertrand [SQL Server MVP]

> I cannot get the IF statement below to work.

That's because you need to use CASE! IF does not belong in a query.

> I wasn't sure if you could assign
> the results of an IF statement to a variable.

That doesn't look like a variable, it looks like a column (alias).

A

9 Dec 2005 8:43 PM

Sherwood

Thanks. Converting it to CASE solved the problem.

--
Sherwood

Show quote

"Aaron Bertrand [SQL Server MVP]" wrote:

> > I cannot get the IF statement below to work.
>
> That's because you need to use CASE! IF does not belong in a query.
>
> > I wasn't sure if you could assign
> > the results of an IF statement to a variable.
>
> That doesn't look like a variable, it looks like a column (alias).
>
> A
>
>
>