insert value into a table | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

insert value into a table

Hi, i need to insert a value into the table by using a condition. How should i write into store procedure? Table Name : Student_Dim
Fields : Stud_ID, Score, Grade Stud_ID Score Grade
001 90
002 80
003 70
004 60 I need to insert a Grade value into this table by using the condition of :
>90 = Grade A
>80 = Grade B
>70 = Grade C
>80 = Grade D How should i write this statement into store procedure? Thank you! Onn
DECLARE @GRADE_NO INT <br />DECLARE @GRADE_TEXT CHAR(1) <br /><br />SET @GRADE_NO = 90<br /><br />SELECT @GRADE_TEXT =<br />CASE <br />WHEN @GRADE_NO &gt;=90 THEN ‘A'<br />WHEN @GRADE_NO &gt;=80 THEN ‘B'<br />WHEN @GRADE_NO &gt;=70 THEN ‘C'<br />WHEN @GRADE_NO &gt;=60 THEN ‘D'<br />END <br /><br />PRINT @GRADE_TEXT<br /><br />–run this from QA it should meet your needs. I’ll let you create the Proc [<img src=’/community/emoticons/emotion-5.gif’ alt=’;)‘ />] <br />–You can also add some more CASES to get more precise i.e. A- or A+ you get the point <br />–By the way give the kids a break if they score 90 on the dot they also get credited an A [<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />]
thank you for you reply. But i can’t apply the "SET @GRADE_NO=90", because i need SQL Server help me to define what student score should belong to which grade. can i use if…else statement? Thank you!
Stud_ID CHAR(3),
Score INT,
Grade CHAR(1)) INSERT @Student_Dim(Stud_ID, Score)
SELECT ‘001’,’90’
SELECT ‘002’,’80’
SELECT ‘003’,’70’
SELECT ‘004’,’60’ SELECT * FROM @Student_Dim UPDATE @Student_Dim
SET Grade =
WHEN Score >=90 THEN ‘A’
WHEN Score >=80 THEN ‘B’
WHEN Score >=70 THEN ‘C’
WHEN Score >=60 THEN ‘D’
END SELECT * FROM @Student_Dim
[email protected] When life gives you a lemon, fire the DBA.
You don’t need If’s you can use CASE, I was just demonstrating an example for you using variables I left the rest as an excercise for you. So here it is then….<br /><br />CREATE PROCEDURE usp_update_studentgrade<br />AS<br />SET NOCOUNT ON<br />DECLARE @ErrorSave int<br />BEGIN TRANSACTION<br />UPDATE Student_Dim<br />SET Grade =<br />CASE <br />WHEN Score &gt;= 90 THEN ‘A'<br />WHEN Score &gt;= 80 THEN ‘B'<br />WHEN Score &gt;= 70 THEN ‘C'<br />WHEN Score &gt;= 60 THEN ‘D'<br />END<br />SELECT @ErrorSave = @@ERROR<br />IF @ErrorSave &lt;&gt; 0 <br />ROLLBACK TRANSACTION <br />ELSE<br />COMMIT TRANSACTION <br />RETURN @ErrorSave<br /><br />–onnon = ‘F’ [<img src=’/community/emoticons/emotion-2.gif’ alt=’:D‘ />]
Thank you for help [:I]