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 DECLARE @GRADE_TEXT CHAR(1) SET @GRADE_NO = 90 SELECT @GRADE_TEXT = CASE WHEN @GRADE_NO >=90 THEN ‘A' WHEN @GRADE_NO >=80 THEN ‘B' WHEN @GRADE_NO >=70 THEN ‘C' WHEN @GRADE_NO >=60 THEN ‘D' END PRINT @GRADE_TEXT –run this from QA it should meet your needs. I’ll let you create the Proc [<img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ />] –You can also add some more CASES to get more precise i.e. A- or A+ you get the point –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!

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…. CREATE PROCEDURE usp_update_studentgrade AS SET NOCOUNT ON DECLARE @ErrorSave int BEGIN TRANSACTION 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 @ErrorSave = @@ERROR IF @ErrorSave <> 0 ROLLBACK TRANSACTION ELSE COMMIT TRANSACTION RETURN @ErrorSave –onnon = ‘F’ [<img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ />]

Thank you for help [:I]

]]>