SQL Server CASE Expression

I've a query like this

There is a main CASE expression, if the result is 1, then I need to execute the other case statements, and else, the select statement below. This query is not working. Can anyone help me

DECLARE @sportCodeID INT;
SELECT @sportCodeID =   sport_code_id FROM jobs dlj开发者_C百科 where dlj.id = id;

(CASE WHEN (SELECT sport_code_id FROM jobs dlj WHERE dlj.id = id) = 1 THEN

CASE WHEN dl.action = 'C' THEN 'Cricker'

WHEN dl.action = 'F' THEN 'FOOTBALL'

)
(ELSE
(SELECT sport_codes from action_codes ac WHERE ac.id = @sportCodeID )

)END [Action]

Here is some code which may accomplish your goal

declare @jobs table(id int identity (1,1), sport_code_Id int)
insert @jobs (sport_code_Id) 
Select 1 UNION ALL 
Select 2 UNION ALL
Select 3 UNION ALL
Select 4

declare @action_codes table (id int identity(1,1), sport_codes char(1))
Insert @action_codes (sport_codes)
Select '1' UNION ALL 
Select 'F' UNION ALL
Select 'C' UNION ALL
Select '4'

Select * from @action_codes
Select * from @jobs

DECLARE @sportCodeID INT; 
SELECT @sportCodeID =   sport_code_id FROM @jobs dlj where dlj.id = 1; 
select @sportCodeID
SELECT 
    CASE sport_codes 
        WHEN 'F' THEN 'Football'
        WHEN 'C' THEN 'Criket'
        ELSE sport_codes
    END
FROM  @action_codes
WHERE id = @sportCodeID

If you continue to have questions please refer to the example below on a case statement using the AdventureWorks databse.

USE AdventureWorks2008;
GO
SELECT   ProductNumber, Category =
      CASE ProductLine
         WHEN 'R' THEN 'Road'
         WHEN 'M' THEN 'Mountain'
         WHEN 'T' THEN 'Touring'
         WHEN 'S' THEN 'Other sale items'
         ELSE 'Not for sale'
      END,
   Name
FROM Production.Product
ORDER BY ProductNumber;
GO