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_DimFields : 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 >=90 THEN ‘A'<br />WHEN @GRADE_NO >=80 THEN ‘B'<br />WHEN @GRADE_NO >=70 THEN ‘C'<br />WHEN @GRADE_NO >=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=’
![Wink ;) ;)](styles/default/xenforo/smilies/wink.png)
![Smile :) :)](styles/default/xenforo/smilies/smile.png)
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!
DECLARE @Student_Dim TABLE(
Stud_ID CHAR(3),
Score INT,
Grade CHAR(1)) INSERT @Student_Dim(Stud_ID, Score)
SELECT ‘001’,’90’
UNION ALL
SELECT ‘002’,’80’
UNION ALL
SELECT ‘003’,’70’
UNION ALL
SELECT ‘004’,’60’ SELECT * FROM @Student_Dim UPDATE @Student_Dim
SET Grade =
CASE
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
MeanOldDBA
[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 >= 90 THEN ‘A'<br />WHEN Score >= 80 THEN ‘B'<br />WHEN Score >= 70 THEN ‘C'<br />WHEN Score >= 60 THEN ‘D'<br />END<br />SELECT @ErrorSave = @@ERROR<br />IF @ErrorSave <> 0 <br />ROLLBACK TRANSACTION <br />ELSE<br />COMMIT TRANSACTION <br />RETURN @ErrorSave<br /><br />–onnon = ‘F’ [<img src=’/community/emoticons/emotion-2.gif’ alt=’
![Big Grin :D :D](styles/default/xenforo/smilies/biggrin.png)
Thank you for help [:I]
]]>