Lookup task based on a variable

This doesn't seem possible but I'll ask anyway...

Can I build a lookup task where the lookup query is based on a variable, rather than hardcoding the SQL staement?

No, there is no support for this.

The best you can do is write a query and then use values in your input data as parameters, to help filter the rows. Seems obvious, but often I have got stuck thinking I wanted a parameterised query, so that results are filtered before I do the lookup, which cannot be done. This is probably not as effcient, but the closest we have.

|||

That's what I figured...

On the bright side, I was able to use another new SQL Server 2005 feature...

SELECT RecId, CAST(COUNT(*) AS Char(1)) [PVL]
FROM VT
WHERE Elect IN (

SELECT Elect
FROM (
SELECT State,PVLE1,PVLE2,PVLE3,PVLE4
FROM StateOptions) opt
UNPIVOT
(Elect For State IN (PVL1, PVL2, PVL3, PVL4)) AS UPV

)

GROUP BY RecId

Of course this would have been much simpler if the options table had been built vertically instead of horizontally!