By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,574 Members | 1,977 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,574 IT Pros & Developers. It's quick & easy.

MSSQL hardcoded dataset from select/stored procedure

Plater
Expert 5K+
P: 7,872
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)
Oct 10 '08 #1
Share this Question
Share on Google+
2 Replies


ck9663
Expert 2.5K+
P: 2,878
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:
Expand|Select|Wrap|Line Numbers
  1. select 'boat' as [TypeName]
  2. UNION ALL
  3. select 'car'
  4. UNION ALL
  5. select 'truck'
  6. UNION ALL
  7. 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
Oct 10 '08 #2

Delerna
Expert 100+
P: 1,134
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.
Oct 11 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.