Reg. getting value - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Reg. getting value

Author

Topic

vmurali
Yak Posting Veteran

88 Posts

Posted - 2006-08-23 : 07:04:04

Hi I have a table grade
marks Grade
90 A+
75 A
60 B
50 C
35 F

now at present i am getting the grade of student by

if(@totmarksn>=90)
begin
select @fgrade=grade from grade
end
else if(@totmarksn>=75 and @totmarksn<90)
begin
select @fgrade=grade from grade
end
else if(@totmarksn>=60 and @totmarksn<75)
begin
select @fgrade=grade from grade
end
else if(@totmarksn>=35 and @totmarksn<60)
begin
select @fgrade=grade from grade
end
else if(@totmarksn<=35)
begin
select @fgrade=grade from grade
end

now is there any way i can get the grade values without hardcoding the ranges

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-08-23 : 07:24:57

Here's one way...

--data (for demo purposes)
declare @grade table (marks int, grade varchar(3))
insert @grade
          select 90, 'A+'
union all select 75, 'A'
union all select 60, 'B'
union all select 50, 'C'
union all select 35, 'F'

--input
declare @totmarksn int
set @totmarksn = 64

--calculation
declare @fgrade varchar(3)
select @fgrade = grade from @grade a where @totmarksn between marks and (select isnull(min(marks), 100) from @grade where marks > a.marks)
print @fgrade

/*results
B
*/

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources