SQL stored procedures use condition from one table

May 26, 2023

For this stored procedure, how to add a condition for value Location?

If tblEmployees.Office = USA and tblEmployees.state = West then Location = CA, else Location = others:

CREATE PROCEDURE spEmployee
AS
BEGIN
    SELECT 
        EmployeeId, Name, Gender, DepartmentName, Location
    FROM 
        tblEmployees
    INNER JOIN 
        tblDepartments ON tblEmployees.EmployeeDepartmentId = tblDepartments.DepartmentId
END

>Solution :

That’s a simple enough CASE expression – something like this:

CREATE PROCEDURE spEmployee
AS
BEGIN
    SELECT 
        EmployeeId, Name, Gender, DepartmentName, 
        Location = CASE
                       WHEN Office = 'USA' AND State = 'West' THEN 'CA'
                       ELSE 'others'
                   END
    FROM 
        tblEmployees e
    INNER JOIN 
        tblDepartments d ON e.EmployeeDepartmentId = d.DepartmentId
END