Re: Nested Iif in View / Stored Procedure

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Candace (anonymous_at_discussions.microsoft.com)
Date: 03/26/04 

Date: Fri, 26 Mar 2004 11:29:26 -0800

I got that far: Here's what I have and I'm not sure what
to do with respect to the expression names (Quarter,
Positive_Infection, Short_CVAD, Long_CVAD) I am figuring I
need to declare them but where do I do this?

Thanks
Candace

Alter PROCEDURE Sub_Annual_Histogram @Start_Date
datetime, @End_Date datetime, @Nursing_Unit nvarchar(25),
@Team nvarchar(50), @Program nvarchar(50), @Type_of_Vein
nvarchar(20)

AS SELECT CASE WHEN ((Month(dbo.Day_CVAD_Date) = 4 OR Month
(dbo.Day_CVAD_Date) = 5 OR Month(dbo.Day_CVAD_Date) = 6))
THEN Quarter = 1
    WHEN ((Month(dbo.Day_CVAD_Date) = 7 OR Month
(dbo.Day_CVAD_Date) = 8 OR Month(dbo.Day_CVAD_Date) = 9)
THEN Quarter = 2
    WHEN ((Month(dbo.Day_CVAD_Date) = 10 OR Month
(dbo.Day_CVAD_Date) = 11 OR Month(dbo.Day_CVAD_Date) = 12)
THEN Quarter = 3
    WHEN ((Month(dbo.Day_CVAD_Date) = 1 OR Month
(dbo.Day_CVAD_Date) = 2 OR Month(dbo.Day_CVAD_Date) = 3))
THEN Quarter = 4
    CASE WHEN (dbo.Infection_Nosocomial = 'Yes' AND
(dbo.Infection_Type = 'Blood' OR dbo.Infection_Type
= 'Both')) THEN Positive_Infection = 1
    ELSE Positive_Infection = 0
    CASE WHEN (dbo.Device_CVAD_Length = 'Short ') THEN
Short_CVAD = 1
    ELSE Short_CVAD = 0
    CASE WHEN (dbo.Device_CVAD_Length = 'Long') THEN
Long_CVAD = 1
    ELSE Long_CVAD = 0
FROM dbo.Device INNER JOIN
    dbo.Day ON
    dbo.Device.Device_New_CVAD_ID = dbo.Day.Day_New_CVAD_ID
     INNER JOIN
    dbo.Patient_Care_Area ON
    dbo.Day.Day_Patient_Care_Area_ID =
dbo.Patient_Care_Area.Patient_Care_Area_ID
     INNER JOIN
    dbo.Infection ON
    dbo.Day.Day_ID = dbo.Infection.Infection_Day_ID
WHERE (dbo.Patient_Care_Area.Patient_Care_Area_Program
LIKE @Program) AND
    (dbo.Patient_Care_Area.Patient_Care_Area_Team_Grouping
LIKE @Team) AND
    (dbo.Patient_Care_Area.Patient_Care_Area_Nursing_Unit
LIKE @Nursing_Unit) AND
    (dbo.Device.Device_Vein_Line LIKE @Type_of_Vein) AND
    (dbo.Day.Day_CVAD_Date BETWEEN @Start_Date AND
@End_Date))

>-----Original Message-----
>IIF statements become CASE statements in SQL Server
stored procedures
>
>--
>Kevin Hill
>President
>3NF Consulting
>
>www.3nf-inc.com/NewsGroups.htm
>
>"Candace" <anonymous@discussions.microsoft.com> wrote in
message
>news:145ad01c41360$f6303880$a101280a@phx.gbl...
>>
>> How do you write a nested Iif statement in a view or a
>> stored procedure?
>>
>> Listed below is what I have in a query in Access, I
>> realize [Day_CVAD_Date] will become dbo.Day_CVAD_Date
and
>> I'm assuming the word Quarter is in my Alias column. So
>> specifically my question is what syntax do I use in
>> my 'Column' column?
>>
>> Quarter: IIf(Month([Day_CVAD_Date])=4 Or Month
>> ([Day_CVAD_Date])=5 Or Month([Day_CVAD_Date])=6,1,IIf
(Month
>> ([Day_CVAD_Date])=7 Or Month([Day_CVAD_Date])=8 Or Month
>> ([Day_CVAD_Date])=9,2,IIf(Month([Day_CVAD_Date])=10 Or
>> Month([Day_CVAD_Date])=11 Or Month([Day_CVAD_Date])
>> =12,3,IIf(Month([Day_CVAD_Date])=1 Or Month
>> ([Day_CVAD_Date])=2 Or Month([Day_CVAD_Date])=3,4,0))))
>>
>> Candace
>
>
>.
>

Relevant Pages

  • Re: Nested Iif in View / Stored Procedure
    ... "Candace" wrote in message ... > FROM dbo.Device INNER JOIN ... >>IIF statements become CASE statements in SQL Server ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Re: List parameters for Stored Procedures
    ... INNER JOIN sys.systypes st ON st.xtype = p.system_type_id ... OP said he wants "to get a list of stored procedures (not system ... I think this is possible by querying the sysobjects table and the ... syscolumns has parameters of procedures. ...
    (microsoft.public.sqlserver.programming)
  • Re: Search/ Stored Procedure.. Not Working
    ... I'm sort of new to Stored Procedures; ... > FROM ((tblAccounts INNER JOIN tblManagerList ON ... > Not sure what is missing, but below is what the Query Analyzer comes ... > PRINT @PrnLine ...
    (microsoft.public.dotnet.general)
  • RE: Stored Procedure with Multiple conditions
    ... > stored procedures and then using them for the reports. ... > C.CostCenterID INNER JOIN ... > entries, and then there may only be 1 entry. ...
    (microsoft.public.sqlserver.programming)
  • Re: Nested Iif in View / Stored Procedure
    ... IIF statements become CASE statements in SQL Server stored procedures ... 3NF Consulting ... "Candace" wrote in message ...
    (microsoft.public.access.adp.sqlserver)