MSSQL hardcoded dataset from select/stored procedure

[Plater]

So this is kind of an odd request.
I want to be able to return a small "hardcoded" dataset from a stored procedure.
Is that even possible?
I want for example a "GetAvailableTypes" store procedure to return like:

[TypeName]
boat
car
truck
suv

Nomally I would consider hardcoding these values into the software I am writing, but if they need to change, would be easier to just change the storedprocedure and not have to release new software.
I suppose I could just make a table, but I was hoping to avoid doing that as there really is only 10-15 items in this list. Seems like a waste of a table.

Any ideas? Or do I need to just make that table. (In the future, the same storeprocedure could be pulling these values from various other tables, thus the extra abstraction out from the software)

[ck9663]

First to answer your question. Yes, it's possible.

Option 1:
Store those value as a comma separated string then convert that string into a result set table. Here's more on that. But this is a lot of work for your SQL Server.

Option 2:
1. Create a table for these values.
2, Create a view for this table and return the value you need.
3. In the future you can keep the name of the view but the underlying query can be change specially if the values would be coming from different tables.

Option 3:
1.Use this query:

select 'boat' as [TypeName]
UNION ALL
select 'car'
UNION ALL
select 'truck'
UNION ALL
select 'suv'

2. Create a view or function for this query.
3. In the future you can keep the name of the view/function but the underlying query can be change specially if the values would be coming from different tables.

Happy coding!

-- CK

[Delerna]

Just some of my thoughts and observations.
(within the context of your post)

Nomally I would consider hardcoding these values into the software I am writing, but if they need to change, would be easier to just change the storedprocedure and not have to release new software.

Great move, I do that sort of thing myself, all the time.
I try hard, never to hard code data like this because, as you say,
It means you don't need to release a new version of your software
just because you added "Hovercraft" to the list.

I suppose I could just make a table, but I was hoping to avoid doing that as there really is only 10-15 items in this list. Seems like a waste of a table.

Hmmmm. I am not disagreeing with you because I am not sure if it is or it isn't.

I've never really hesitated using a table for this purpose.
I have the thought that it is data and tables are best suited to store data.

As a table its easy to allow users to add their own items into the list.
Also you can use the table so that your other database objects (views, sp's, udf's) can automatically handle the addition/removal of items in the list.