Solved

Generic GetValue function

Posted on 2014-09-23
8
219 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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

Veeam gives away 10 full conference passes

Veeam is a VMworld 2017 US & Europe Platinum Sponsor. Enter the raffle to get the full conference pass. Pass includes the admission to all general and breakout sessions, VMware Hands-On Labs, Solutions Exchange, exclusive giveaways and the great VMworld Customer Appreciation Part

Question has a verified solution.

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

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
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 documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

626 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