Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Generic GetValue function

Posted on 2014-09-23
8
209 Views
Last Modified: 2014-09-25
I have a table called tblOptions with lots of columns in. There is only one row.

If at all possible, I am looking to write a generic scalar-valued function that returns the value of one column in the above mentioned table. The column name will be passed into the function as a parameter.

I would like to avoid having to have a CASE statement in the function as this will require maintenance.

I am looking to use this function for column defaults when creating tables as I'd rather that approach than triggers.

Is this possible?
0
Comment
Question by:JedNebula
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40338963
You could use the CHOOSE function instead.
0
 
LVL 37

Expert Comment

by:Neil Russell
ID: 40338977
Would be far more sensible to have a table with just 2 columns, first column holding the old column name as the Primary key and the second as the value. A very simple select statement then works AND you are future proof.

What you have is a very poor design.
0
 
LVL 1

Author Comment

by:JedNebula
ID: 40339016
Thank you both.

Neilsr, what you have said makes perfect sense. I'm not sure why I didn't do things that way. It was a long time ago. The next project I start I definitely will change to that method - thank you.

I'm afraid that the (albeit nasty) setup I have in place currently, is too aged and too frequently used to be changed. I have not used the ChOOSE function before. I have had a quick look, but I'm not seeing how I might use that to provide a solution. Could you throw an example together for me?
0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40339025
CHOOSE(calculation that results in a value from 1 to 4 for example, Column-Result1, Column-Result2, Column-Result3, Column-Result4)
0
 
LVL 1

Author Comment

by:JedNebula
ID: 40339080
Thinking about this further, I'm not sure either of your suggestions could be used as column defaults.

For example, I might have another table called tblSalesLine containing a column called VAT_Rate. On row insert, if I want the value to be the DefaultVATRate value from the tblOptions table, the column default has to be either a function OR a column/expression made up from tblSalesLine members.

I don't think you can simply use SELECT DefaultVATRate  FROM tblOptions as the default value. Neither could you use the CHOOSE function.

Unless I'm missing something?
0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 500 total points
ID: 40339309
This would be easy to do using dynamic SQL ... but you can't use dynamic SQL in a function.

Therefore, I don't see any way to do this except to use a CASE statement with the associated maintenance when a column is added (are columns really added that often)?
0
 
LVL 1

Author Comment

by:JedNebula
ID: 40339317
Exactly.
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40339363
It'd be easy enough to write code to generate the function using all columns currently in the table.  And you could create the function with SCHEMABINDING, forcing you to adjust it when the table changed.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SSMS Opening Mode 9 20
Compare a numeric value to a varchar w/o getting an error 4 21
First Max value 3 29
RESTORE MASTER DATABASE -- NOW 2 20
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question