Answered by:
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?
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