sql server - SQL CASE statement for if -
i've looked around , cannot find answer this. far i'm aware i'm using correctly i'm missing keeps coming 'incorrect syntax near keyword 'case''
i'm trying take 2 values, , depending on 'word' return value of 1-5. these multiplied give me 'rating'
declare @pr int declare @ir int declare @r int declare @probabilityrating varchar(max) declare @impactrating varchar(max) set @probabilityrating = 'high' set @impactrating = 'medium' case @probabilityrating when 'very low' @pr = 1 when 'low' @pr = 2 when 'medium' @pr = 3 when 'high' @pr = 4 when 'very high' @pr = 5 end case @impactrating when 'very low' @ir = 1 when 'low' @ir = 2 when 'medium' @ir = 3 when 'high' @ir = 4 when 'very high' @ir = 5 end set @r = @ir * @pr where going wrong?!
since you're reusing case, maybe makes sense have values in table (fiddle):
create table ratings ( value int ,description varchar(20) ) insert ratings values (1,'very low') ,(2,'low') ,(3,'medium') ,(4,'high') ,(5,'very high') and assign variables select...
select @ir = r.value ratings r r.[description] = @impactrating select @pr = r.value ratings r r.[description] = @probabilityrating alternatively, create temp table (fiddle):
select d.* #ratings (values ('very low',1) ,('low',2) ,('medium',3) ,('high',4) ,('very high',5) ) d([description], value) select @ir = r.value #ratings r r.[description] = @impactrating select @pr = r.value #ratings r r.[description] = @probabilityrating as syntax issue, seems you're confusing sql case switch (from other languages) you'd branch execute different code. pretty similar, that's understandable. behave differently, though. according the documentation (emphasis mine):
case
evaluates list of conditions and returns one of multiple possible result expressions.
that's say, case statement resolves value. assign value variable, so:
set @pr = case @probabilityrating when 'very low' 1 when 'low' 2 when 'medium' 3 when 'high' 4 when 'very high' 5 end
Comments
Post a Comment