Reading the system settings using stored procedure/function

Stop hard-coding the constants in a stored procedure. Instead, you should read the value from your "settings" table or returning the constant value by a function call. In C# and other programming languages, you can declare constant in a class and use it anywhere in the program. But, in MSSQL, you can't do that.

To get the constant or settings, you may try to do this:

• Create a function which returns the value (could be constant or read a field from a table).
• Create a stored procedure and returns the value through OUTPUT parameter.

There are some factors which you might have to decide how to design the stored procedure/function:

• Should the value configurable by the the user? If no, you may return the value by a function call. If the value is user configurable, then, you need a "setting table" to store the value.
• If the value must be stored in a table, do you store the value in a generic table structure (with prop_name and prop_value fields) OR do you need to design a "settings" table which contains only 1 record (with the appropriate field name which can reflect the setting)?

In our database design, we have a generic table that store setting. In case the values are frequently use, we will design a setting table to store the user configured values.