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
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.
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.
Member
1 Points
5 Posts
stored procedure with if(?)
Jun 01, 2020 04:52 PM|Kjelllannoo|LINK
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?
Participant
1140 Points
299 Posts
Re: stored procedure with if(?)
Jun 01, 2020 05:23 PM|imapsp|LINK
Hi,
Can you post an example with a small sample of data from the tables and the respective expected result?
Member
1 Points
5 Posts
Re: stored procedure with if(?)
Jun 01, 2020 05:31 PM|Kjelllannoo|LINK
My DB is called "Oldtimer"
Then I have
dbo.autos
dbo.contact
dbo.deelnemers
dbo.evenementen
dbo.leden
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.
Participant
1140 Points
299 Posts
Re: stored procedure with if(?)
Jun 01, 2020 07:02 PM|imapsp|LINK
Try:
Hope this help
Member
1 Points
5 Posts
Re: stored procedure with if(?)
Jun 01, 2020 07:05 PM|Kjelllannoo|LINK
What does the c.QtyEvents do? and what is the CTE_Count?
Member
1 Points
5 Posts
Re: stored procedure with if(?)
Jun 01, 2020 07:07 PM|Kjelllannoo|LINK
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.
Member
1 Points
5 Posts
Re: stored procedure with if(?)
Jun 01, 2020 07:23 PM|Kjelllannoo|LINK
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.