3

I have a scalar function which returns a VARCHAR of a column name.

I would like to use this result to base a select query upon, so:

SELECT dbo.udf_GetColName('Val1', ColumnFromThisTable, etc) AS myCol
FROM tbl_ThisTable WHERE ...

At the moment, this is correctly listing the actual output of the UDF, the column name, for each value.

What I would like is for the select statement to return the value of the column returned from the function, so:

SET @sql = 'SELECT ' + dbo.udf_GetColName('Val1', ColumnFromThisTable, etc) + ' AS myCol
FROM tbl_ThisTable WHERE ... '

And run EXEC sp_executesql

Is there a better way than the dynamic SQL route? Some way that SQL can query that column as output from the UDF?

EDIT TO ADD

This is where the business need to manage the rules upon which the output is selected hence they need to be in an updateable table. If they're hardcoded into the SELECT of Table-Valued functions then it is no longer the business that control them.

So yes, the query is very customisable but would be, in this instance "a good thing".

Additionally, there is a finite number of parameters into udf_GetColName. It receives a source column name, a source column value, which makes a lookup in the rules table. Should the rule find a match of that column to that value, an output column is selected and returned otherwise a default output column is used. This is the column that needs to be selected, hence could potentially be quite different to the input or input value.

As said, I'm happy to hear any other ideas and of course if this is silly and another route should be picked!

FINAL EDIT FOR THE DAY BEFORE I GO HOME

I'm looking for a way to select which columns to use from tbl_ThisTable, based upon other columns values in tbl_ThisTable.

These rules as to which column to use need to be easily updateable within a table - a major limitation is the interface / front end to the database - we can only return straight datasets so can't use the front end to make this decision / concatenate multiple datasets etc...

If there's a good way to deploy these rules, which can be updated within the database without rewriting code, I'd love to hear them. I'm just testing this at the moment, so design is flexible.

RemarkLima
  • 11,639
  • 7
  • 37
  • 56
  • 5
    What you are trying to achieve is hard because it's bad practice to have metadata about the database in the database. While sometimes excusable, the reason you need to do it is probably a mistake in database design rather than a practical requirement. – Niels Keurentjes Apr 16 '13 at 15:50
  • 1
    What @Neils said. This is approaching the http://en.wikipedia.org/wiki/Inner-platform_effect – gbn Apr 16 '13 at 15:52
  • @Niels It's to try and setup some rules as to what data should be output based upon criteria - however the management of these rules needs to be placed with the business, hence in an updateable table rather than hardcoded stright into the views / Table-Valued functions. It's not ideal, but sadly necessary. – RemarkLima Apr 16 '13 at 15:53
  • Short answer, no. This whole approach is inelegant. If you define a real world scenario where you think dynamically choosing the column is useful, you (or I) can define a more elegant non-dynamic approach that is simpler to write, easier to read and faster to run. – Jodrell Apr 16 '13 at 15:55
  • El;egance is a very poor way to write SQL code. It is the last thing you want. YOu want to get data that is correct first, you want to protect the security of teh data second and you want thebest performance you can get. Performant code is often inelegant. Just dump the compet of elegance when dealing with databases. It is harmful. What you are trying to do is not a good idea. it will be slow and it will be buggy. You are trying to save developement time at the expense of everything a database is good for. It is short-sighted. – HLGEM Apr 16 '13 at 15:55
  • @gbn, nice link, for me anyway. A new name for an old and very common idea. – Jodrell Apr 16 '13 at 15:59
  • @Jodrell - Would you be so kind as to post an answer to define a more elegant non-dynamic approach please? Clearly `EXEC` and dynamic SQL exist - for this reason or not? P.S. Not being sarcastic or anythjing, genuinely interested in any other solutions - Even if the overall recomendation is to hardcode all these choices, and if it's changed, recode everything – RemarkLima Apr 16 '13 at 16:02
  • Does the client have a fininte number of parameters that they can pass to `udf_GetColName`? What does `udf_GetColName` do? – Jodrell Apr 16 '13 at 16:10
  • @Jodrell there is a finite number of parameters into `udf_GetColName`. It receives a source column name, a source column value, which makes a lookup in the rules table. Should the rule find a match of that column to that value, an `output column` is selected and returned otherwise a default `output column` is used. This is the column that needs to be selected, hence could potentially be quite different to the input. – RemarkLima Apr 16 '13 at 16:14
  • You are going to end up with Dynamic SQL for this, so you should probably just accept that now and move on to how to use it safely and securely. See here (http://stackoverflow.com/questions/1246760/how-should-i-pass-a-table-name-into-a-stored-proc/1246848#1246848) for an example of that (this example is for Table Names, but the principles will work just as well for Column Names). Some even consider this to be elegant as well... – RBarryYoung Apr 16 '13 at 17:00
  • @RBarryYoung Thanks for the reality check - I'm thinking you are correct. I really wanted to check if there's an alternative... Ultimately, if I build a SQL statement in application code or a UDF in SQL itself doesn't differ to much. I'm pretty au fait with sanitizing data, too many years of web dev! Thank you for the links, I'll have a read now – RemarkLima Apr 18 '13 at 07:54

2 Answers2

3

The only other way that I can think of is to use a case statement:

select (case when udf_GetColName('Val1', . . .) = 'Col1' then Col1
             when udf_GetColName('Val1', . . .) = 'Col2' then Col2
             . . .
       ) as MyCol
from tbl_ThisTable . . .

The dynamic SQL seems simpler. In both cases, though, be careful about types. With the case, this would return the type of the first then. With the dynamic SQL, the type of the return value depends on the type of the underlying column.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Thanks for the thoughts - case is going to get pretty messy and as you say, may as well go dynamic at that point ;) – RemarkLima Apr 16 '13 at 15:55
1

Rewind,

rather than

SET @sql = 
    'SELECT ' + dbo.udf_GetColName('Val1', ColumnFromThisTable, etc) +
        ' AS myCol FROM tbl_ThisTable WHERE ... '

why not just do

SELECT Val1 [myCol] FROM tbl_ThisTable WHERE ...

in the first place?

Make the caller do the branching.

What I mean is, generate your SQL in the client using an ORM of your choice.

EDIT

use sp_executesql and read Sommarskog

More generally, I believe your data could be normalised in such a way that you could achieve what you want with join conditions rather than dynamic column selection. Without the actual schema and example data I can't quite visualise the answer.

Jodrell
  • 34,946
  • 5
  • 87
  • 124
  • Sadly, limitation of the setup where I work. The interface can only call straight functions / sp's hence all this work needs to be done inside SQL. Otherwise I'd happily return all the potential data from SQL and have the front end decide based upon the rules table. – RemarkLima Apr 16 '13 at 16:09
  • @RemarkLima what data is in `tbl_ThisTable` are the columns attributes of an entity or, is the `tbl_ThisTable` a denormailsed lookup table? – Jodrell Apr 16 '13 at 16:15
  • the `tbl_ThisTable` is the data table, hence the columns are the data columns - the `udf_GetColName` will return one of the column names from `tbl_ThisTable` based upon specific values of `tbl_ThisTable`. – RemarkLima Apr 16 '13 at 16:17
  • @RemarkLima, are the parameters that can be passed to `dbo.udf_GetColName` finite or is it a lookup to an extensible table? – Jodrell Apr 16 '13 at 16:18
  • The parameters are finite, currently 3 but this is just testing to see if this can work... – RemarkLima Apr 16 '13 at 16:21
  • @RemarkLima, I honestly believe you'd be better off with 3, or more static statements, although, if your are intent, I find Sommarskog to be the difinitve reference http://www.sommarskog.se/dynamic_sql.html – Jodrell Apr 16 '13 at 16:41
  • Thanks for the link, I know what you mean about trying to do joins. Ultimately, I can't figure out a way to select what column to select based upon a query... So I think I'll head down the dynamic route. Thanks for the help and input – RemarkLima Apr 18 '13 at 07:57