locked
stored procedure with if(?) RRS feed

  • Question

  • User-343568592 posted

    Good evening all,

    I'm trying to write stored procedure called 'spVulRisicos, this should full ip a column named risicos.

    If they participated in 0 events the risk should be set to NULL/0

    If they were in 1 or 2 the risk is 1

    if they were in 3 or 4 the risk is 2

    and if it is 5 or more the risk should be set to 3...

    I currently allready have a normal query that shows me how many cars participated in an event.

    select *
    from autos a
    where autonr in
    (select autonr from deelnemers
    group by autonr
    having count(*) >= 2)

    someone who could help me with this?

    Monday, June 1, 2020 4:52 PM

Answers

  • User452040443 posted

    Try:

    with CTE_Count as
    (
        select autonr, count(1) as QtyEvents 
        from deelnemers
        group by autonr
    )
    
    update a
    set a.risico = 
        case 
            when c.QtyEvents is null then 0
            when c.QtyEvents < 3 then 1
            when c.QtyEvents < 5 then 2
            else 3
        end
    from autos a
    left join CTE_Count c on c.autonr = a.autonr
    

    Hope this help

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, June 1, 2020 7:02 PM

All replies

  • User452040443 posted

    Hi,

    Can you post an example with a small sample of data from the tables and the respective expected result?

    Monday, June 1, 2020 5:23 PM
  • User-343568592 posted

    My DB is called "Oldtimer"

    Then I have 

    dbo.autos

    • autonr (PK, int, not null)
    • typenaam(char(50) null),
    • bouwjaar(int null)
    • waarde(int null)
    • passagiers(int null)
    • risico(smallint null)

    dbo.contact

    • lidnr (fk, int null)
    • email (varchar(255)null

    dbo.deelnemers

    • autonr(PK,FK,int not null)
    • evenementnr(PK,FK,int notnull)

    dbo.evenementen

    • evenementnr(pk, int,not null)
    • evenementnaam(varchar(50) null)
    • evenementdatum(date,null)
    • locatie(varchar(50) null)
    • soort(varchar(50) null)
    • prijs(int null)

    dbo.leden

    • lidnr(PK, int not null)
    • lidnaam (varchar50 null)
    • soort, char6 null
    • lidnaam (varchar100 null)
    • inschrijving (date, null)
    • uitschrijving(date, null)

    Then if I use my query

    select *
    from autos a
    where autonr in
    (select autonr from deelnemers
    group by autonr
    having count(*) >= 2)

    I see all the cars that partipated in 2 events, if I change it to 3 i can see all those who went to 3 events and so on.

    with the SP I have to be able to automaticly fill in the colmun risico based on how many events the card has been into.

    Monday, June 1, 2020 5:31 PM
  • User452040443 posted

    Try:

    with CTE_Count as
    (
        select autonr, count(1) as QtyEvents 
        from deelnemers
        group by autonr
    )
    
    update a
    set a.risico = 
        case 
            when c.QtyEvents is null then 0
            when c.QtyEvents < 3 then 1
            when c.QtyEvents < 5 then 2
            else 3
        end
    from autos a
    left join CTE_Count c on c.autonr = a.autonr
    

    Hope this help

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, June 1, 2020 7:02 PM
  • User-343568592 posted

    What does the c.QtyEvents do? and what is the CTE_Count?

    Monday, June 1, 2020 7:05 PM
  • User-343568592 posted

    I did this;

    create procedure spVulRisico
    as
    set nocount on
    with CTE_Count as
    (
    select autonr, count(1) as QtyEvents
    from deelnemers
    group by autonr
    )

    update a
    set a.risico =
    case
    when c.QtyEvents is null then 0
    when c.QtyEvents < 3 then 1
    when c.QtyEvents < 5 then 2
    else 3
    end
    from autos a
    left join CTE_Count c on c.autonr = a.autonr

    But then it gives me back

    Msg 319, Level 15, State 1, Procedure spVulRisico, Line 4 [Batch Start Line 43]
    Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

    Monday, June 1, 2020 7:07 PM
  • User-343568592 posted

    Good evening Imapsp, I

    Thanks for all your help, I've what I was doing wrong, I set a constraint on the table which limited me to only allow 1,2 and 3, but 0 was seen as a wrong value.

    Monday, June 1, 2020 7:23 PM