asked on

using a case statement column in sql server as a parameter

I have a view in sql server 2008 where I have a column called EmployeeStatus that stores either Y or N in that column
I'm creating a stored procedure where i'm using a case state like shown below where I'm creating a new column called EmployeeStatus2

In my stored procedure I have a parameter called EmployeeStatus2 in the where clause. But when I do the code shown below, SSMS gives me a message saying EmployeeStatus2 is not a valid column.

Any idea how I can use a column i created in a case statement as a parameter column?

SELECT [Region]
      ,[EmployeeStatus]
      ,CASE WHEN [EmployeeStatus] = 'Y' 
                      THEN 'Employed' 
                      ELSE 'Unemployed' 
                 END AS EmployeeStatus2
FROM [TestDatabase].[dbo].[TestView]
WHERE EmployeeStatus2 = @EmployeeStatus2

Open in new window

ASKER CERTIFIED SOLUTION

Jim Horn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Anoo S Pillai

Another option -

SELECT * 
FROM ( SELECT [Region]
		,[EmployeeStatus]
		,CASE WHEN [EmployeeStatus] = 'Y' 
		THEN 'Employed' 
		ELSE 'Unemployed' 
		END AS EmployeeStatus2
	FROM [TestDatabase].[dbo].[TestView]
) AS TempData 
WHERE EmployeeStatus2 = @EmployeeStatus2

Open in new window

Jim Horn

Thanks for the grade. Good luck with your project. -Jim