Using the CASE Statement : Select « Select Query « SQL Server / T-SQL






Using the CASE Statement


1> create table employee(
2>     ID          int,
3>     name        nvarchar (10),
4>     salary      int,
5>     start_date  datetime,
6>     city        nvarchar (10),
7>     region      char (1))
8> GO
1>
2> insert into employee (ID, name,    salary, start_date, city,       regio
3>               values (1,  'Jason', 40420,  '02/01/94', 'New York', 'W')
4> GO

(1 rows affected)
1> insert into employee (ID, name,    salary, start_date, city,       regio
2>               values (2,  'Robert',14420,  '01/02/95', 'Vancouver','N')
3> GO

(1 rows affected)
1> insert into employee (ID, name,    salary, start_date, city,       regio
2>               values (3,  'Celia', 24020,  '12/03/96', 'Toronto',  'W')
3> GO

(1 rows affected)
1> insert into employee (ID, name,    salary, start_date, city,       regio
2>               values (4,  'Linda', 40620,  '11/04/97', 'New York', 'N')
3> GO

(1 rows affected)
1> insert into employee (ID, name,    salary, start_date, city,       regio
2>               values (5,  'David', 126,    '10/05/98', 'Vancouver','W')
3> GO

(1 rows affected)
1> insert into employee (ID, name,    salary, start_date, city,       regio
2>               values (6,  'James', 7006,  '09/06/99', 'Toronto',  'N')
3> GO

(1 rows affected)
1> insert into employee (ID, name,    salary, start_date, city,       regio
2>               values (7,  'Alison',9062,  '08/07/00', 'New York', 'W')
3> GO

(1 rows affected)
1> insert into employee (ID, name,    salary, start_date, city,       regio
2>               values (8,  'Chris', 2000,  '07/08/01', 'Vancouver','N')
3> GO

(1 rows affected)
1> insert into employee (ID, name,    salary, start_date, city,       regio
2>               values (9,  'Mary',  60020,  '06/09/02', 'Toronto',  'W')
3> GO

(1 rows affected)
1>
2> select * from employee
3> GO
ID          name       salary      start_date              city       regio
----------- ---------- ----------- ----------------------- ---------- -----
          1 Jason            40420 1994-02-01 00:00:00.000 New York   W
          2 Robert           14420 1995-01-02 00:00:00.000 Vancouver  N
          3 Celia            24020 1996-12-03 00:00:00.000 Toronto    W
          4 Linda            40620 1997-11-04 00:00:00.000 New York   N
          5 David              126 1998-10-05 00:00:00.000 Vancouver  W
          6 James             7006 1999-09-06 00:00:00.000 Toronto    N
          7 Alison            9062 2000-08-07 00:00:00.000 New York   W
          8 Chris             2000 2001-07-08 00:00:00.000 Vancouver  N
          9 Mary             60020 2002-06-09 00:00:00.000 Toronto    W

(9 rows affected)
1>
2> --Using the CASE Statement
3> drop procedure sp_demo
4> GO
1>
2> CREATE PROCEDURE sp_Demo
3>    @StartDate DATETIME,
4>    @ID INT = 1
5> WITH ENCRYPTION
6> AS
7>    IF @StartDate > GETDATE()
8>       RETURN 1
9>    IF @ID < 0
10>       RETURN 2
11>
12>    SELECT "Result" =
13>    CASE WHEN salary > 1000 THEN     "> 1000"
14>         WHEN salary = 2000 THEN     "= 2000"
15>         WHEN salary < 8000 THEN     "< 8000"
16>    END
17>       FROM Employee
18>       WHERE Id <> @ID
19>         AND Start_Date <> @StartDate
20> GO
1>
2>
3> DECLARE @Start_Date_In smalldatetime
4> DECLARE @Id_In INT
5> SET @Start_Date_In = "24 March 2001"
6> SET @ID_In = 1
7> EXECUTE sp_Demo @Start_Date_In, @ID_In
8>
9>
10> drop table employee
11> GO
Result
------
> 1000
> 1000
> 1000
< 8000
> 1000
> 1000
> 1000
> 1000

(8 rows affected)
1>
           
       








Related examples in the same category

1.Use regular expressions to retrieve table data
2.Sorting Results
3.Using Functions in Queries
4.Using TOP in select statement
5.Select a single row
6.Group by, having and order
7.Use your function in select statement
8.Queries Involving Null Values
9.Get full details of all departments using *
10.SELECT: return literal values that are just part of an expression
11.An expression-type result set can be aliased and treated as if it were a table
12.Select with 'Case when'