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.

From where Maine and Florida will comes? Are you just looking for `UNION`? — Ilyes, Sep 18 '19 at 19:32
It might be a union, Florida and Maine are not in the database at all and I don't want to add them. — TroySteven, Sep 18 '19 at 19:33
Can I create a temp table and UNION them? If anyone can provide the code using my example I will accept their answer immediately. — TroySteven, Sep 18 '19 at 19:34
select C1 from [your_table] Union Select 'Florida' Union Select 'Maine' ; this piece of code will do it for you. — junketsu, Sep 18 '19 at 19:35

score 1 · Accepted Answer · answered Sep 18 '19 at 19:35

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.

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

1 Answers1