CASE Function in SQL Server (Part 1)

This article explores case function in SQL server (part 1) with straightforward explanations and useful context. It also highlights common questions and important details to consider.

This is the most common use of the case function, in which you can create scalar values based on the list of conditions given.

How CASE Function in SQL Server (Part 1) Works

Suppose we have the following table with columns id (employee number), [First name] (name), [Last name] (them) and gender (gender). Now, we want to create a prefix (Mr, Ms) in front of each name, based on the value of Gender column, do the following:

 use tempdb 
 go 
 if exists (select * from dbo.sysobjects where id = object_id (N '[emp]') 
 and OBJECTPROPERTY (id, N'IsUserTable ') = 1) 
 drop table [emp] 
 GO 
 create table Emp (id int, [First name] varchar (50), 
 [Last name] varchar (50), gender char (1)) 
 go 
 insert into Emp (id, [First name], [Last name], gender) 
 values ​​(1, 'John', 'Smith', 'm') 
 insert into Emp (id, [First name], [Last name], gender) 
 values ​​(2, 'James', 'Bond', 'm') 
 insert into Emp (id, [First name], [Last name], gender) 
 values ​​(3, 'Alexa', 'Mantena', 'f') 
 insert into Emp (id, [First name], [Last name], gender) 
 values ​​(4, 'Shui', 'Qui', 'f') 
 insert into Emp (id, [First name], [Last name], gender) 
 values ​​(5, 'William', 'Hsu', 'm') 
 insert into Emp (id, [First name], [Last name], gender) 
 values ​​(6, 'Danielle', 'Stewart', 'F') 
 insert into Emp (id, [First name], [Last name], gender) 
 values ​​(7, 'Martha', 'Mcgrath', 'F') 
 insert into Emp (id, [First name], [Last name], gender) 
 values ​​(8, 'Henry', 'Fayol', 'm') 
 insert into Emp (id, [First name], [Last name], gender) 
 values ​​(9, 'Dick', 'Watson', 'm') 
 insert into Emp (id, [First name], [Last name], gender) 
 values ​​(10, 'Helen', 'Foster', 'F') 
 go 

Now, create a [Full name] column to determine which prefix should be 'Mr.' or 'Ms.', based on the value in the column Gender.

Select [id],[Full Name] = case Gender
 when 'm' then 'Mr. '+[First name]+ ' '+[Last name]
 when 'f' then 'Ms. '+[First name]+ ' '+[Last name]
 end
from Emp

This procedure returns the result as shown below:

 id Full Name 
 ----------- ---------------- 
 1 Mr. John Smith 
 2 Mr. James Bond 
 3 Ms. Alexa Mantena 
 4 Ms. Shui Qui 
 5 Mr. William Hsu 
 6 Ms. Danielle Stewart 
 7 Ms. Martha Mcgrath 
 8 Mr. Henry Fayol 
 9 Mr. Dick Watson 
 10 Ms. Helen Foster 

If you add a row with the value NULL to the gender column, you will not see any names returned in the result set.

Insert the following row into the emp table:

use tempdb go insert into Emp (id,[First name],[Last name], gender ) values (11,'Tom','Gabe',NULL) go

Now create a [Full name] column to determine the prefix prefix each name is 'Mr.' or 'Ms.', based on the value in the column Gender:

Select [id],[Full Name] = case Gender when 'm' then 'Mr. '+[First name]+ ' '+[Last name] when 'f' then 'Ms. '+[First name]+ ' '+[Last name] end from Emp

The result returned is as follows:

 id Full Name 
 ----- ----------------------- 
 1 Mr. John Smith 
 2 Mr. James Bond 
 3 Ms. Alexa Mantena 
 4 Ms. Shui Qui 
 5 Mr. William Hsu 
 6 Ms. Danielle Stewart 
 7 Ms. Martha Mcgrath 
 8 Mr. Henry Fayol 
 9 Mr. Dick Watson 
 10 Ms. Helen Foster 
 11 NULL 

However, we need to display the Full Name of the employee, even if there is no value on Gender.

To do this, you need to use the CASE function with the ELSE clause. Execute the query as follows:

Select [id],[Full Name] = case Gender
 when 'm' then 'Mr. '+[First name]+ ' '+[Last name]
 when 'f' then 'Mz. '+[First name]+ ' '+[Last name]
 else [First name]+ ' '+[Last name]
 end
from Emp

The result returned is as follows:

 id Full Name 
 ------ --------------------- 
 1 Mr. John Smith 
 2 Mr. James Bond 
 3 Mz. Alexa Mantena 
 4 Mz. Shui Qui 
 5 Mr. William Hsu 
 6 Mz. Danielle Stewart 
 7 Mz. Martha Mcgrath 
 8 Mr. Henry Fayol 
 9 Mr. Dick Watson 
 10 Mz. Helen Foster 
 11 Tom Gabe 

In the above two examples, you find that the conditions are either Male, Female, or None. However, in many cases you will need to use multiple conditions, multiple operators at the same time to return a value.

Add the [Marital Status] column to the table and update the value as below:

use tempdb
go
alter table Emp add [Marital Status] char(1) -- S-Single M-Married
go
Update Emp set [Marital Status]='S' where id in (1,5,8)
Update Emp set [Marital Status]='M' where [Marital Status] is NULL
Go

Suppose we want to display the employee name with the preceding prefix that makes it easy to determine their marital status. Execute the query as follows:

Select [id],[Full Name] = case
when Gender ='m' and [marital status] ='S' then 'MR. '+[First name]+ ' '+[Last name]
when Gender ='m' and [marital status] ='M' then 'Mr. '+[First name]+ ' '+[Last name]
when Gender ='f' and [marital status] ='S' then 'Ms. '+[First name]+ ' '+[Last name]
when Gender ='f' and [marital status] ='M' then 'Mrs. '+[First name]+ ' '+[Last name]
else [First name]+ ' '+[Last name]
 end
from Emp

The result returned is:

 id Full Name 
 ----------- -------------------- 
 1 MR. John Smith 
 2 Mr. James Bond 
 3 Mrs. Alexa Mantena 
 4 Mrs. Shui Qui 
 5 MR. William Hsu 
 6 Mrs. Danielle Stewart 
 7 Ms. Martha Mcgrath 
 8 MR. Henry Fayol 
 9 Mr. Dick Watson 
 10 Mrs. Helen Foster 
 11 Tom Gabe 

Suppose we have the following table

 use tempdb go if exists (select * from dbo.sysobjects where id = object_id (N '[emp]') and OBJECTPROPERTY (id, N'IsUserTable ') = 1) drop table [emp] GO create table Emp (id int, [First name] varchar (50), [Last name] varchar (50), Salary money) go insert into Emp (id, [First name], [Last name], salary) values ​​(1, 'John', 'Smith ', 120000) insert into Emp (id, [First name], [Last name], salary) values ​​(2,' James', 'Bond', 95000) insert into Emp (id, [First name], [Last name ], salary) values ​​(3, 'Alexa', 'Mantena', 200000) insert into Emp (id, [First name], [Last name], salary) values ​​(4, 'Shui', 'Qui', 36000) insert into Emp (id, [First name], [Last name], salary) values ​​(5, 'William', 'Hsu', 39000) insert into Emp (id, [First name], [Last name], salary) values ​​(6, 'Danielle', 'Stewart', 50000) insert into Emp (id, [First name], [Last name], salary) values ​​(7, 'Martha', 'Mcgrath', 400000) insert into Emp ( id, [First name], [Last name], salary) values ​​(8, 'Henry', 'Fayol', 75000) insert into Emp (id, [First name], [Last name], salary) values ​​(9, 'Dick', 'Watson', 91000) insert into Emp (id, [First name], [Last name], salary) values ​​(10, 'Helen', 'Foster', 124000) go 

And now I want to create a Tax column based on the following salary

 Select [id], [Full Name] = [First name] + [Last name], Salary, Tax = case When salary between 0 and 36000 then Salary * .24 When salary between 36000 and 450000 then Salary * .28 When salary between 45000 and 75000 then Salary * .30 When salary between 75000 and 150000 then Salary * .32 else Salary * .40 end from Emp 

This function will produce the result:

 id Full Name Salary Tax ----------- ----------------- ----------- ------ -------- 1 JohnSmith 120000.00 95000.00 jamesbond 26600.000000 33600.000000 2 3 4 ShuiQui AlexaMantena 56000.000000 36000.00 200000.00 8640.000000 5 WilliamHsu 39000.00 50000.00 DanielleStewart 14000.000000 10920.000000 6 7 8 HenryFayol 112000.000000 MarthaMcgrath 400000.00 75000.00 91000.00 DickWatson 25480.000000 21000.000000 9 10 HelenFoster 124000.00 34720.000000 

Suppose we have the table below in Books:

 use tempdb go if exists (select * from dbo.sysobjects where id = object_id (N '[Books]') and OBJECTPROPERTY (id, N'IsUserTable ') = 1) drop table [Books] GO create table Books (Bookid int, Title varchar (100), Authorname varchar (100), state char (2)) go into Books (Bookid, Title, Authorname, state) values ​​(1, 'The Third Eye', 'Lobsang Rampa', 'CA') insert into Books (Bookid, Title, Authorname, state) values ​​(2, 'Service Oriented Architecture For Dummies',' Judith Hurwitz ',' NJ ') insert into Books (Bookid, Title, Authorname, state) values ​​(3,' Business Reference for Students and Professionals', 'Ray Myers',' NY ') insert into Books (Bookid, Title, Authorname, state) values ​​(4,' More Java Gems', 'Dwight Deugo', 'FL') insert into Books (Bookid, Title, Authorname, state) values ​​(5, 'Six Sigma Workbook For Dummies',' Craig Gygi ',' FL ') insert into Books (Bookid, Title, Authorname, state) values ​​(6,' Performance Appraisals : How to Achieve Top Results', 'Priscilla A. Glidden', 'NC') insert into Book s (Bookid, Title, Authorname, state) values ​​(7, 'Talent Management: From Competencies to Organizational Performance', 'John Smith', 'FL') insert into Books (Bookid, Title, Authorname, state) values ​​(8, 'Using Unix', 'Howard Johnson', 'CT') insert into Books (Bookid, Title, Authorname, state) values ​​(9, 'Mastering Oracle', 'Erina Zolotrova', 'CT') insert into Books (Bookid, Title, Authorname, state) values ​​(10, 'How to become CEO', 'Olga Zohaskov', 'NY') goes 

To query all the values in the table we use the query function below:

 Select * from Books 

This function will produce the result as shown below

Suppose we want to display the entire number of books in the order of states: first is NY, then CA, NJ, CT and FL. You can do this using the CASE function as shown below:

 select Title, Authorname, state from Books order by case when state = 'NY' then 1 when state = 'CA' then 2 when state = 'NJ' then 3 when state = 'CT' then 4 when state = 'FL' then 5 else 6 end 

This function will produce the following result:

 Title Authorname state ----------------------------------------------- ------------- --------------------- ----- Business Reference for Students and Professionals Ray Myers NY How to become Third Officer Olga Zohaskov NY The Third Eye Lobsang Rampa CA Depties Service Oriented Architecture For Judith Hurwitz NJ Using Unix Howard Johnson CTO Eolotrova CT More Java Gems Dwight Deugo FL Six Sigma Workbook For Dummies Craig FL Talent Management: From Competencies to Organizational Per John Smith FL Performance Appraisals: How to Achieve Top Results Priscilla A. Glidden NC 

Suppose we have the following table

 set quoted_identifier off go to use tempdb go if exists (select * from dbo.sysobjects where id = object_id (N '[emp]') and OBJECTPROPERTY (id, N'IsUserTable ') = 1) drop table [emp] GO create table Emp (id int, [First name] varchar (50), [Last name] varchar (50), Salary money, state char (2)) go insert into Emp (id, [First name], [Last name], salary, State) values ​​(1, 'John', 'Smith', 120000, 'WA') insert into Emp (id, [First name], [Last name], salary, State) values ​​(2, 'James',' Bond ', 95000,' OR ') insert into Emp (id, [First name], [Last name], salary, State) values ​​(3,' Alexa ',' Mantena ', 200000,' WY ') insert into Emp ( id, [First name], [Last name], salary, State) values ​​(4, 'Shui', 'Qui', 36000, 'CO') insert into Emp (id, [First name], [Last name], salary, State) values ​​(5, 'William', 'Hsu', 39000, 'NE') insert into Emp (id, [First name], [Last name], salary, State) values ​​(6, 'Danielle', 'Stewart', 50000, 'TX') insert into Emp (id, [First name], [Last name], salary, State) values ​​(7, 'Martha', 'Mcgrath', 400000, 'PA') inser t into Emp (id, [First name], [Last name], salary, State) values ​​(8, 'Henry', 'Fayol', 75000, 'NJ') insert into Emp (id, [First name], [ Last name], salary, State) values ​​(9, 'Dick', 'Watson', 91000, 'NY') insert into Emp (id, [First name], [Last name], salary, State) values ​​(10, 'Helen', 'Foster', 124000, 'AK') go 

And now want to create more TimeZone columns based on State (US state)

 select id, [First name], [Last name], salary, Timezone = case when state in ('WA', 'OR', 'NE', 'CO') then 'Pacific' when state in ('NY', 'NJ', 'VT', 'ME', 'NH', 'MA', 'RI', 'CT', 'PA', 'DE', 'MD', 'DC', 'VA', 'W' ',' MI ',' IN ',' OH ',' KY ',' NC ',' GA ',' FL ') then' Eastern 'when state in (' MT ',' ID ',' WY ', 'UT', 'CO', 'AZ', 'NM') then 'Mountain' when state in ('ND', 'SD', 'NE', 'KS', 'OK', 'TX', 'MN', 'MN' ',' IA ',' MO ',' AR ',' LA ',' WI ',' IL ',' TN ',' MS ',' AL ') then' Central 'when state in (' AK ') then 'Alaskan' when state in ('HA') then 'Hawaii' end from emp 

The above function will produce the following result

 id First name Last name salary Timezone ------------------------------------------- --- 1 John Smith 120000.00 Pacific 2 James Bond 95000.00 Pacific 3 Alea Mantena 200000.00 Mountain 4 Shui Qui 36000.00 Pacific 600.00 Central 6 Martha Mcgrath 400000.00 Eastern 8 Henry Fayol 75000.00 Eastern 9 Dick Watson 91000.00 Eastern 10 Helen Foster 124000.00 Alaskan 

Now we want to see all the information in the lines with Eastern and Mountain Timezone

 select * from (select id, [First name], [Last name], salary, Timezone = case when state in ('WA', 'OR', 'NE', 'CO') then 'Pacific' when state in ( 'NY', 'NJ', 'VT', 'ME', 'NH', 'MA', 'RI', 'CT', 'PA', 'DE', 'MD', 'DC', 'VA' ',' WV ',' MI ',' IN ',' OH ',' KY ',' NC ',' GA ',' FL ') then' Eastern 'when state in (' MT ',' ID ', 'WY', 'UT', 'CO', 'AZ', 'NM') then 'Mountain' when state in ('ND', 'SD', 'NE', 'KS', 'OK', 'TX' ',' MN ',' IA ',' MO ',' AR ',' LA ',' WI ',' IL ',' TN ',' MS ',' AL ') then' Central 'when state in ( 'AK') then 'Hawaii' end from emp) as mytype where TimeZone in ('Mountain', 'eastern') 

The result of this function is as follows

 id First name Last name salary Timezone 3 Alexa Mantena 200000.00 Mountain 7 Martha Mcgrath 400000.00 Eastern 8 Henry Fayol 75000.00 Eastern 9 Dick Watson 91000.00 Eastern 

Now we have the table above and you want to display the average value of the salary based on the time zone (Timezone).

 select avg (salary) as AverageSalary, Timezone = case when state in ('WA', 'OR', 'NE', 'CO') then 'Pacific' when state in ('NY', 'NJ', 'VT' , 'ME', 'NH', 'MA', 'RI', 'CT', 'PA', 'DE', 'MD', 'DC', 'VA', 'WV', 'MI', ' IN ',' OH ',' KY ',' NC ',' GA ',' FL ') then' Eastern 'when state in (' MT ',' ID ',' WY ',' UT ',' CO ' , 'AZ', 'NM') then 'Mountain' when state in ('ND', 'SD', 'NE', 'KS', 'OK', 'TX', 'MN', 'IA', ' MO ',' AR ',' LA ',' WI ',' IL ',' TN ',' MS ',' AL ') then' Central 'when state in (' AK ') then' Alaskan 'when state in ('HA') then 'Hawaii' end from emp group by case when state in ('WA', 'OR', 'NE', 'CO') then 'Pacific' when state in ('NY', 'NJ' , 'VT', 'ME', 'NH', 'MA', 'RI', 'CT', 'PA', 'DE', 'MD', 'DC', 'VA', 'WV', ' MI ',' IN ',' OH ',' KY ',' NC ',' GA ',' FL ') then' Eastern 'when state in (' MT ',' ID ',' WY ',' UT ' , 'CO', 'AZ', 'NM') then 'Mountain' when state in ('ND', 'SD', 'NE', 'KS', 'OK', 'TX', 'MN', ' IA ',' MO ',' AR ',' LA ',' WI ',' IL ',' TN ',' MS ',' AL ') then' Central 'when state in (' AK ') then' Alaskan 'when state in (' HA ') then' Hawaii 'end 

This function will produce the result as shown below

 AverageSalary TimeZone --------------------------- 124000.00 Alaskan 50000.00 Central 188666.6666 Eastern 200000.00 Mountain 72500.00 Pacific 

Now you just want to see the Eastern and Alaskan time zones from the above results. We can use the HAVING clause as shown below

 select avg (salary) as AverageSalary, Timezone = case when state in ('WA', 'OR', 'NE', 'CO') then 'Pacific' when state in ('NY', 'NJ', 'VT' , 'ME', 'NH', 'MA', 'RI', 'CT', 'PA', 'DE', 'MD', 'DC', 'VA', 'WV', 'MI', ' IN ',' OH ',' KY ',' NC ',' GA ',' FL ') then' Eastern 'when state in (' MT ',' ID ',' WY ',' UT ',' CO ' , 'AZ', 'NM') then 'Mountain' when state in ('ND', 'SD', 'NE', 'KS', 'OK', 'TX', 'MN', 'IA', ' MO ',' AR ',' LA ',' WI ',' IL ',' TN ',' MS ',' AL ') then' Central 'when state in (' AK ') then' Alaskan 'when state in ('HA') then 'Hawaii' end from emp group by case when state in ('WA', 'OR', 'NE', 'CO') then 'Pacific' when state in ('NY', 'NJ' , 'VT', 'ME', 'NH', 'MA', 'RI', 'CT', 'PA', 'DE', 'MD', 'DC', 'VA', 'WV', ' MI ',' IN ',' OH ',' KY ',' NC ',' GA ',' FL ') then' Eastern 'when state in (' MT ',' ID ',' WY ',' UT ' , 'CO', 'AZ', 'NM') then 'Mountain' when state in ('ND', 'SD', 'NE', 'KS', 'OK', 'TX', 'MN', ' IA ',' MO ',' AR ',' LA ',' WI ',' IL ',' TN ',' MS ',' AL ') then' Central 'when state in (' AK ') then' Alaskan 'when state in (' HA ') then' Hawaii 'end having case when state in (' WA ',' OR ',' NE ', 'CO') then 'Pacific' when state in ('NY', 'NJ', 'VT', 'ME', 'NH', 'MA', 'RI', 'CT', 'PA', 'DE' ',' MD ',' DC ',' VA ',' WV ',' MI ',' IN ',' OH ',' KY ',' NC ',' GA ',' FL ') then' Eastern ' when state in ('MT', 'ID', 'WY', 'UT', 'CO', 'AZ', 'NM') then 'Mountain' when state in ('ND', 'SD', 'NE' ',' KS ',' OK ',' TX ',' MN ',' IA ',' MO ',' AR ',' LA ',' WI ',' IL ',' TN ',' MS ', 'AL') then 'Central' when state in ('AK') then 'Alaskan' when state in ('HA') then 'Hawaii' end in ('Eastern', 'Alaskan') 

The result of the function is shown in the following figure:

 AverageSalary TimeZone 
 ----------------------------- 
 124000.00 Alaskan 
 188666.6666 Eastern 

Suppose we have the following table

 set quoted_identifier off go to use tempdb go if exists (select * from dbo.sysobjects where id = object_id (N '[emp]') and OBJECTPROPERTY (id, N'IsUserTable ') = 1) drop table [emp] GO create table Emp (id int, [First name] varchar (50), [Last name] varchar (50), Salary money, state char (2)) go insert into Emp (id, [First name], [Last name], salary, State) values ​​(1, 'John', 'Smith', 120000, 'WA') insert into Emp (id, [First name], [Last name], salary, State) values ​​(2, 'James',' Bond ', 95000,' OR ') insert into Emp (id, [First name], [Last name], salary, State) values ​​(3,' Alexa ',' Mantena ', 200000,' WY ') insert into Emp ( id, [First name], [Last name], salary, State) values ​​(4, 'Shui', 'Qui', 36000, 'CO') insert into Emp (id, [First name], [Last name], salary, State) values ​​(5, 'William', 'Hsu', 39000, 'NE') insert into Emp (id, [First name], [Last name], salary, State) values ​​(6, 'Danielle', 'Stewart', 50000, 'TX') insert into Emp (id, [First name], [Last name], salary, State) values ​​(7, 'Martha', 'Mcgrath', 400000, 'PA') inser t into Emp (id, [First name], [Last name], salary, State) values ​​(8, 'Henry', 'Fayol', 75000, 'NJ') insert into Emp (id, [First name], [ Last name], salary, State) values ​​(9, 'Dick', 'Watson', 91000, 'NY') insert into Emp (id, [First name], [Last name], salary, State) values ​​(10, 'Helen', 'Foster', 124000, 'AK') go 

And now we need to have 6 tables to store employee IDs of different time zones as shown below

 if exists (select * from dbo.sysobjects where id = object_id (N '[eastern]') and objectproperty (id, N'isusertable ') = 1) drop table [eastern] go create table eastern (id int) if exists ( select * from dbo.sysobjects where id = object_id (N '[mountain]') and objectproperty (id, N'isusertable ') = 1) drop table [mountain] go create table mountain (id int) if exists (select * from dbo.sysobjects where id = object_id (N '[hawaii]') and objectproperty (id, N'isusertable ') = 1) drop table [hawaii] go create table hawaii (id int) if exists (select * from dbo.sysobjects where id = object_id (N '[central]') and objectproperty (id, N'isusertable ') = 1) [central] drop table created table central (id int) if exists (select * from dbo.sysobjects where id = object_id (N '[alaskan]') and objectproperty (id, N'isusertable ') = 1) drop table [alaskan] go to create alaskan table (id int) if exists (select * from dbo.sysobjects where id = object_id (N '[pacific]') and objectproperty (id, N'isusertable ') = 1) drop table [paci fic] go create table Pacific (id int) go insert into pacific (id) values ​​(1) insert into pacific (id) values ​​(2) insert into mountain (id) values ​​(3) insert into mountain (id) values ​​(4) ) insert into central (id) values ​​(5) insert into central (id) values ​​(6) insert into eastern (id) values ​​(7) insert into eastern (id) values ​​(8) insert into eastern (id) values ​​(9 ) insert into alaskan (id) values ​​(10) go 

When needed to know the entire Eastern time zone employee, you will definitely have to execute a simple query statement as shown below.

 select e.id, [First Name], [Last name], Salary, State 
 from emp e join eastern ee on e.id = ee.id 

The above query statement will return the following result

 id First name Last name salary Timezone 
 ------------------------------------------------ 
 7 Martha Mcgrath 400000.00 PA 
 8 Henry Fayol 75000.00 NJ 
 9 Dick Watson 91000.00 NY 

So now suppose we need to create a script that allows us to put a time zone into a variable and display the result based on the value of that variable. This can be done using clauses and CASE functions as follows:

 declare @group varchar (10) 
 set @ group = 'Pacific' 
 select ee.id, ee. [First Name], ee. [Last Name], Salary, State, @group as TimeZone from emp ee 
 left join mountain m on m. [id] = ee. [id] 
 kết nối không phải alaskan a trên a. [id] = ee. [id] 
 hăm bên trái h trong h. [id] = ee. [id] 
 trái bên ngoài c trên c. [id] = ee. [id] 
 pacific left kết nối p trên p. [id] = ee. [id] 
 trái bên ngoài e vào e. [id] = ee. [id] 
 where ee.id in (case @group 
 when 'Then' Eastern e.id 
 when 'Mountain' Then m.id 
 when 'Pacific' then p.id 
 when 'Alaskan' then a.id 
 when 'Hawaii' Then h.id 
 khi 'Central' Then c.id 
 end) 

The above scenario will have the following results:

 id First name Last name salary state TimZone ------------------------------------------ ------------- 1 John Smith 120000.00 WA Pacific 2 James Bond 95000.00 OR Pacific 

The above script can be written in the following procedure:

 create procedure emp_timezone @Zone varchar (10) 
 as 
 select ee.id, ee. [First Name], ee. [Last Name], Salary, State, @Zone as TimeZone from emp ee 
 left join mountain m on m. [id] = ee. [id] 
 kết nối không phải alaskan a trên a. [id] = ee. [id] 
 hăm bên trái h trong h. [id] = ee. [id] 
 trái bên ngoài c trên c. [id] = ee. [id] 
 pacific left kết nối p trên p. [id] = ee. [id] 
 trái bên ngoài e vào e. [id] = ee. [id] 
 where ee.id in (case @Zone 
 when 'Then' Eastern e.id 
 when 'Mountain' Then m.id 
 when 'Pacific' then p.id 
 when 'Alaskan' then a.id 
 when 'Hawaii' Then h.id 
 khi 'Central' Then c.id 
 end) 

And execute the above created function with the command:

 exec emp_timezone 'Eastern' 

The function will produce the result:

 id First name Last name salary state TimZone 
 -------------------------------------------------- --- 
 7 Martha Mcgrath 400000.00 PA Eastern 
 8 Henry Fayol 75000.00 NJ Eastern 
 9 Dick Watson 91000.00 NY Eastern 

• SELECT command in SQL Server
• UPDATE command in SQL Server