stored procedure with if(?)

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?

Answer 1

User452040443 posted

Hi,

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

Answer 2

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.

Answer 3

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

Answer 4

User-343568592 posted

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

Answer 5

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.

Answer 6

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.