Manually add records to the result of a SQL select query via stored procedure call using SQL Server

Question

CREATE PROCEDURE [dbo].[GetAllLocations] 

AS
BEGIN
    SET NOCOUNT ON;

    SELECT DISTINCT [Location] from Emp WHERE [Location] IS NOT NULL;
END

I need a stored procedure that will pull a result from one select query and then I need to add 2 additional results to that query. I don't want to insert the two additional results into a table I just want to "hardcode them in".

So basically if the above query has this result:

New York
Kansas 
California
New Mexico 

I want to manually add Maine and Florida to that list but I don't want to add those records to the "Emp" table in that query. I'm assuming some sort of temp table might do the job but I couldn't find any solution online and I am not familiar with temp tables.

Answer 1

You can use union, as in:

SELECT [Location] 
FROM Emp
WHERE [Location] IS NOT NULL
UNION  -- on purpose to remove duplicates
SELECT Location
FROM (VALUES ('Maine'), ('Florida')) v(Location);

That said, I don't see a good reason to make this a stored procedure. You should make it either a view or a user-defined table function. That say, the code can be used from inside a SELECT query.