Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Generic GetValue function

Posted on 2014-09-23
8
Medium Priority
?
227 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
[X]
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
  • 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
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

 
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 70

Accepted Solution

by:
Scott Pletcher earned 2000 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 70

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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
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…
Via a live example, show how to shrink a transaction log file down to a reasonable size.

705 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