Ashish Patel

IF THEN in SQL SELECT Statement

I recently came across the CASE WHEN statement work Similar to IF statement into SQL SELECT , Maybe you’ll find it useful.

Create table called Student using SQL Query:

CREATE TABLE [dbo].[Student](
	[StudentID] [int] NULL,
	[Marks] [float] NULL
)

Insert some data into student:

INSERT INTO [Student] ([StudentID],[Marks]) VALUES (1,30)
INSERT INTO [Student] ([StudentID],[Marks]) VALUES (1,65)
INSERT INTO [Student] ([StudentID],[Marks]) VALUES (1,90)
INSERT INTO [Student] ([StudentID],[Marks]) VALUES (1,55)
INSERT INTO [Student] ([StudentID],[Marks]) VALUES (1,70)

We can use CASE WHEN statement instead of IF. Here is Query: 

SELECT [StudentID]
      ,[Marks]
      ,case When [Marks] <65 and &#91;Marks&#93; >49
            then 'Pass' 
            When [Marks] <75 and &#91;Marks&#93; >64
            then 'Credit' 
            When [Marks] <85 and &#91;Marks&#93; >74
            then 'Distinction' 
            When [Marks] <101 and &#91;Marks&#93; >84
            then 'High Distinction' 
            else 'Fail' 
                  end As Result    
  FROM [Student]

Thanks

Posted in JQueryTagged , , 1 Comment

One response to “IF THEN in SQL SELECT Statement”

  1. OR something like this…

    just to remind “between” function is inclusive thus the difference in range values.

    first 2 conditions will eliminate (a) invalid scores — anything over 100

    and define anything over 84 as ‘High Distinction’
    then anything Less then 50 will be failed

    SELECT [StudentID],[Marks],
    case
    When [Marks] > 100 then ‘Invalid’
    When [Marks] > 84 then ‘High Distinction’
    When [Marks] between 75 and 84 then ‘Distinction’
    When [Marks] between 65 and 74 then ‘Credit’
    When [Marks] between 50 and 64 then ‘Pass’
    else ‘Fail’
    end As Result
    FROM Student

    Reply

Leave a Reply