How to mock multiple rows in a SELECT in a stored procedure?

Question

This might be a silly question but I have tried several things and can't obtain the result I want.

I am currently testing a very tiny API that consumes data from an SQL Server database. In order to test it I am using a stored procedure that "mocks" a row and returns it. Like this:

CREATE PROCEDURE ONE
    (@FRUITCODE VARCHAR(12))
AS
BEGIN
    SET NOCOUNT ON;

    SELECT
        'BANANA' as NAME
    WHERE 
        @FRUITCODE > 0;
END;

This works fine to simulate one row, but I want to return more rows like this:

NAME
---------
BANANA
STRAWBERRY
MELON

I have tried

CREATE PROCEDURE TWO
    (@FRUITCODE VARCHAR(12))
AS
BEGIN
    SET NOCOUNT ON;

    SELECT
        'BANANA', 'STRAWBERRY', 'MELON' AS NAME
    WHERE @FRUITCODE > 0;
END;

But that results in multiple columns, one is NAME and the others have a blank column name.

I also tried

CREATE PROCEDURE THREE
    (@FRUITCODE VARCHAR(12))
AS
BEGIN
    SET NOCOUNT ON;

    SELECT 'BANANA' AS NAME
    WHERE @FRUITCODE > 0;

    SELECT 'STRAWBERRY' AS NAME
    WHERE @FRUITCODE > 0;

    SELECT 'MELON' AS NAME
    WHERE @FRUITCODE > 0;
END;

But this generates separate "tables" instead of a single one with multiple rows.

Lastly I tried

CREATE EPROCEDURE FOUR
    (@FRUITCODE VARCHAR(12))
AS
BEGIN
    SET NOCOUNT ON;

    SELECT
        'BANANA' AS NAME,
        'STRAWBERRY' AS NAME,
        'MELON' AS NAME
    WHERE @FRUITCODE > 0;
END;

But this returns multiple "NAME" columns.

How can I return several rows? I am using SQL Server.

Answer 1

We need to use the VALUES table constructor, basically an inline 'invented' table, see the docs on this.
It can also be done with UNION ALL but that is more verbose.

SELECT *
FROM (VALUES
        ('BANANA'),
        ('STRAWBERRY'),
        ('MELON')
) AS v (NAME)
WHERE @FRUITCODE > 0