Solved

Generic GetValue function

Posted on 2014-09-23
8
207 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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

770 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