I want to use SQL stored in a field to join 2 tables RefTable and DataTable:

This picture is RefTable (DataTable also has LinkFld1 and LinkFld2):

My (non-working) code looks like this:

Select D.*, R.Output
from DataTable D 
join RefTable R
     on D.LinkFld1=R.Linkfld1 --Works as intended
     and D.linkfld2 exec(R.Linkfld2) --Doesn't work.

Intended Results:

|DataTable.LinkFld1|DataTable.LinkFld2|RefTable.OutputFld|  
|123               |INS               |Alpha             |
|123               |CON               |Beta              |
|234               |Anything          |Gamma             |

How do I rewrite the SQL query to use the text in LinkFld2 as part of the sql statement?

CodePudding user response：

Splitting your LinkFld2 into separate pattern and like/not-like indicator fields may get you on the right track. Something like:

DECLARE @Map TABLE (
    LinkFld1 VARCHAR(100),
    LinkFld2Not BIT,
    LinkFld2Pattern VARCHAR(100),
    OutputFld VARCHAR(100)
)
INSERT @Map
VALUES
    ('123', 0, '%INS%', 'Alpha'),
    ('123', 1, '%INS%', 'Beta'),
    ('234', 0, '%', 'Gamma') -- %% changed to %

DECLARE @Data TABLE (
    LinkFld1 VARCHAR(100),
    LinkFld2 VARCHAR(100)
)
INSERT @Data
VALUES
    ('123', 'INS'),
    ('123', 'CON'),
    ('234', 'Anything')

SELECT D.*, M.OutputFld
FROM @Data D
LEFT JOIN @Map M
    ON M.LinkFld1 = D.LinkFld1
    AND (CASE WHEN D.LinkFld2 LIKE LinkFld2Pattern THEN 0 ELSE 1 END) = M.LinkFld2Not

See this db<>fiddle

CodePudding user response：

You would need to use dynamic SQL. For example, you could write...

DECLARE @Query nvarchar(200)

SET @Query = (
    SELECT TOP 1 'SELECT * FROM DataTable WHERE Linkfld1 = '   CAST(LinkFld1 as nvarchar(200))   ' AND LinkFld2 '   LinkFld2
    FROM RefTable
)

EXEC(@Query)

The @Query variable gets populated with SELECT * FROM DataTable WHERE Linkfld1 = 123 AND LinkFld2 like '%INS%' and is then passed to EXEC(), which runs it and returns the results.

I wrote it to to return TOP 1. You'd need to decide how you're going to call the query to populate the variable, and get it to return one row, each time.

Fiddle