0

I have a small table tbl_a that is something like

id fieldName tableName
1 field1 tbl_1
2 field2 tbl_1
3 field3 tbl_2

and I want to be able to come up with a function or proc or something where I can specify the fieldId from tbl_a and then query the correct field and table from that. something like

select * from my_function(3)

should end up being equivalent to

select field3 from tbl_2

I've been looking into dynamic sql and user functions but can't seem to figure out how to feed the results of the query into another.

EDIT:

As @Larnu correctly surmised, there is a larger task hiding behind the one posed in the original question. The premise is this:

  • tblArchive stores the values of certain "static" fields (found in other tables) with a Date attached. If/when these fields are changed in their original table, then a record is inserted into tblArchive. More-or-less an audit table.

eg: in tbl_accounts, AdjustmentFactor field (fieldId=3) for accountId=1 changes from 1.0 to 0.5 on '2022-06-10'. Insert into tblArchive (fieldId, accountId, date, value) values (3,1,'2022-06-10',0.5)

  • tblArchive was only created in 2019. I've been tasked with back-filling records from 2017 on. That is, to insert records that would have been inserted had tblArchive existed in 2017.
  • In order to backfill, I have to look into the real audit tables (for previous example this would be tblAccountsAudit for that particular fieldId).
  • The fields of interest and their respective tables are given in tblFields. tblFields would be tbl_a from the original question and for the example given we'd have something like
id fieldName tableName
3 AdjustmentFactor tbl_accounts

Assume also that the design is what it is and I have no power to overhaul the design/structure of the database.

Rusty Shackleford
  • 53
  • 4
  • If my_function() returns a table then you can do what you just described. – Ross Bush Jun 10 '22 at 17:33
  • This type of requirement/design is normally a strong indicator of an [XY Problem](//xyproblem.info). – Thom A Jun 10 '22 at 17:36
  • `FUNCTION`s also, *cannot* run dynamic SQL, you would have to use a `PROCEDURE`, which can't be used in a `SELECT`. – Thom A Jun 10 '22 at 17:38
  • just curious, in which scenario you will need the `select * from my_function(3)` when you can do `select field3 from tbl_2` – LONG Jun 10 '22 at 17:42
  • Yeah there's about 30 records in tbl_a and there's a longer, more complicated query I'm hiding that I want to repeat for those ~30 field/table pairs, and copy and pasting this query 30 times just to edit a few terms offends my aesthetic sensibilities. Edit: and thanks for all the quick replies everyone – Rusty Shackleford Jun 10 '22 at 17:46
  • Seems like this is an XY Problem, and you should actually be asking about this other query. – Thom A Jun 10 '22 at 17:49
  • Fair enough. Unfortunately it's difficult to summarize and for compliance reasons I can't be too specific -- but I'll see if I can't put an appendix at the end of my Question that explains the motivation and describes what a mwe would look like – Rusty Shackleford Jun 10 '22 at 17:53

1 Answers1

0

It sounds like you need something like this.

CREATE FUNCTION myfunction (
    @key INT
)
RETURNS TABLE
AS
RETURN
    SELECT 
        id,
fieldName,
tableName
    FROM
        tbl_a
    WHERE
        id = @key;

This will give return the table you are after through a function.

NateBradshaw
  • 9
  • 1