CODEWITHSUNDEEP
sql-serverstored-procedurescase
Csharp
Csharp

Reputation: 2982

SQL case statement in a stored procedure

I have a SQL Server stored proc that contains a CASE statement. However, I need to append the values if multiple conditions are true.

So if a particular record has an Invalid Date And Mileage exceeded, I would like both values to be displayed in the NotArchiveableReason column.

How would I accomplish that? 

, CASE 
    WHEN DateOfLoss < PolicyStartDate THEN 'Invalid Date'
    WHEN MilesDriven > TotalMilesAllowed THEN 'Mileage exceeded'
    WHEN LossStatusCode != 'R' THEN 'Status code is Review'
    Else 'Unknown issue'
    END
    As NotArchiveableReason

Upvotes: 3

Views: 21031

Answers (5)

Kiril Rusev
Kiril Rusev

Reputation: 753

Why dont you go with 3 case statements and then in a new query CONCAT them?

, CASE 
WHEN DateOfLoss < PolicyStartDate THEN 'Invalid Date'
END as 1

WHEN MilesDriven > TotalMilesAllowed THEN 'Mileage exceeded'
END as 2

WHEN LossStatusCode != 'R' THEN 'Status code is Review'
END as 3

 select 1+2+3 As NotArchiveableReason

Upvotes: 0

Mike
Mike

Reputation: 3811

If you want to concatenate results like Invalid Date, Mileage Exceeded then you may be looking for something like this.

ISNULL(
    NULLIF(
        STUFF(
            CASE WHEN DateOfLoss < PolicyStartDate THEN ', Invalid Date' ELSE '' END
            + CASE WHEN MilesDriven > TotalMilesAllowed THEN ', Mileage exceeded' ELSE '' END
            + CASE WHEN LossStatusCode != 'R' THEN ', Status code is Review' ELSE '' END
        , 1, 2, '')
    ,'')
, 'Unknown issue')
As NotArchiveableReason

The STUFF() removes the leading comma. The NULLIF() converts the empty string to null. The ISNULL() will populate "Unknown Issue" when none of the CASE statement conditions are met.

Upvotes: 3

M.Ali
M.Ali

Reputation: 69504

,  CASE   WHEN DateOfLoss < PolicyStartDate THEN 'Invalid Date '        ELSE '' END
+  CASE   WHEN MilesDriven > TotalMilesAllowed THEN 'Mileage exceeded ' ELSE '' END
+  CASE   WHEN LossStatusCode != 'R' THEN 'Status code is Review '      ELSE '' END
   CASE   WHEN DateOfLoss >= PolicyStartDate OR MilesDriven <= TotalMilesAllowed
                OR LossStatusCode = 'R'
          THEN 'Unknown issue' END                              As NotArchiveableReason

Upvotes: 1

aweis
aweis

Reputation: 5596

How about expand the result set and let the application handle it

, CASE WHEN DateOfLoss < PolicyStartDate THEN 1 ELSE 0 END as InvalidDate
, CASE WHEN MilesDriven > TotalMilesAllowed THEN 1 ELSE 0 END as MileageExceeded
, CASE WHEN LossStatusCode != 'R' THEN 1 ELSE 0 END as StatusCodeIsReview

Then if all is zero it is an unknown issue?

EDIT

You can try this then. This is using an inner select to first find the problems and then combine it afterwards. In this sollution you can add many different checks in the inner select, and then combine them as you like in the outer.

select case when tmp.InvalidDate is null and tmp.MileageExceeded is null and tmp.StatusCodeIsReview is null then 'Unknown issue' else
stuff
(
    (
        COALESCE(', ' + NULLIF(tmp.InvalidDate, ''), '') + 
        COALESCE(', ' + NULLIF(tmp.MileageExceeded, ''), '') + 
        COALESCE(', ' + NULLIF(tmp.StatusCodeIsReview, ''), '')), 1, 2, '')
end as NotArchiveableReason from
(
    select *
    , CASE WHEN DateOfLoss < PolicyStartDate THEN 'Invalid Date' ELSE NULL END as InvalidDate
    , CASE WHEN MilesDriven > TotalMilesAllowed THEN 'Mileage Exceeded' ELSE NULL END as MileageExceeded
    , CASE WHEN LossStatusCode != 'R' THEN 'Status Code Is Review' ELSE NULL END as StatusCodeIsReview
    from MyTest
) as tmp

Upvotes: 0

S Koppenol
S Koppenol

Reputation: 205

, CASE WHEN DateOfLoss < PolicyStartDate THEN 'Invalid Date ' ELSE '' END
+ CASE WHEN MilesDriven > TotalMilesAllowed THEN 'Mileage exceeded ' ELSE '' END
+ CASE WHEN LossStatusCode != 'R' THEN 'Status code is Review ' ELSE '' END
+ CASE WHEN NOT
       (   DateOfLoss < PolicyStartDate
       AND MilesDriven > TotalMilesAllowed
       AND LossStatusCode != 'R') THEN 'Unknown issue ' ELSE '' END
    As NotArchiveableReason

Upvotes: 1

Related Questions