CODEWITHSUNDEEP
sql-server
Alex Lemesios
Alex Lemesios

Reputation: 532

SQL - Convert results of SELECT statement to a parameter in stored procedure

consider the following stored procedure : 

alter procedure getRisks @id int, @convertedRiskValue int
as
select top 1 table1.id, idRisk, riskValue from table1
left join table2
on table1.id = table2.id
where table1.id= @id
order by table2.id desc

enter image description here

, then put it into @convertedRiskValue and return it from the select statement.

Any ideas ?

Upvotes: 0

Views: 48

Answers (2)

Niraj Rajpurohit
Niraj Rajpurohit

Reputation: 34

Try This :

alter procedure getRisks @id int, @convertedRiskValue int
as
select top 1 table1.id, CASE WHEN RiskValue >= 0 AND RiskValue <= 250 THEN 1 
WHEN RiskValue >= 251 and RiskValue <= 500 THEN 2
WHEN RiskValue >= 501 and RiskValue <= 750 THEN 3
WHEN RiskValue >= 751 and RiskValue <= 1000 THEN 4 end as idRiskType
 , riskValue from table1
left join table2
on table1.id = table2.id
where table1.id= @id
order by table2.id desc

Upvotes: 1

Dr. X
Dr. X

Reputation: 2930

SELECT 
   riskValue,
   CASE 
     WHEN riskValue > -1 AND riskValue < 251 THEN 1
     WHEN riskValue > 250 AND riskValue < 501 THEN 2
     WHEN riskValue > 500 AND riskValue < 751 THEN 3
     WHEN riskValue > 750 AND riskValue < 1001 THEN 4
  END AS idRisk
FROM Table

Upvotes: 1

Related Questions