stored procedure with if(?)[Answered] RSS

• • Reply
  Kjelllannoo

  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?

• • Reply
  imapsp

  Participant

  1030 Points

  267 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?

  https://www.imoveisemexposicao.com.br/imoveis-venda-guarulhos-residencial-casa
• • Reply
  Kjelllannoo

  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

  • 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.

• • Reply
  imapsp

  Participant

  1030 Points

  267 Posts

  Re: stored procedure with if(?)

  Jun 01, 2020 07:02 PM|imapsp|LINK

  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

  https://www.imoveisemexposicao.com.br/imoveis-venda-guarulhos-residencial-casa
• • Reply
  Kjelllannoo

  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?

• • Reply
  Kjelllannoo

  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.

• • Reply
  Kjelllannoo

  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.