SQL - How can I return a value from a different table base on a parameter?

Question

SQL - How can I return a value from a different table base on a parameter First time poster, long time reader: I am using a custom Excel function that allows be to pass parameters and build a SQL string that returns a value. This is working fine. However, I would like to choose among various tables based on the parameters that are passed.

At the moment I have two working functions with SQL statements look like this:

_______FUNCTION ONE________

<SQLText>
SELECT PRODDTA.TABLE1.T1DESC as DESCRIPTION
FROM PRODDTA.TABLE1
WHERE PRODDTA.TABLE1.T1KEY = '&amp;PARM02'</SQLText>

_______FUNCTION TWO________

<SQLText>
SELECT PRODDTA.TABLE2.T2DESC as DESCRIPTION 
FROM PRODDTA.TABLE2 
WHERE PRODDTA.TABLE2.T2KEY = '&amp;PARM02'</SQLText>

So I am using IF logic in Excel to check the first parameter and decide which function to use.

It would be much better if I could do a single SQL statement that could pick the right table based on the 1st parameter. Logically something like this:

_______FUNCTIONS COMBINED________

IF '&amp;PARM02' = “A” THEN

SELECT PRODDTA.TABLE1.T1DESC as DESCRIPTION 
FROM PRODDTA.TABLE1 
WHERE PRODDTA.TABLE1.T1KEY = '&amp;PARM02'

ELSE IF '&amp;PARM02' = “B” THEN

SELECT PRODDTA.TABLE2.T2DESC as DESCRIPTION 
FROM PRODDTA.TABLE2 
WHERE PRODDTA.TABLE2.T2KEY = '&amp;PARM02'

ELSE
DESCRIPTION = “”

Based on another post Querying different table based on a parameter I tried this exact syntax with no success

<SQLText>
IF'&amp;PARM02'= "A"
BEGIN
SELECT PRODDTA.F0101.ABALPH as DESCRIPTION
FROM PRODDTA.F0101 
WHERE PRODDTA.F0101.ABAN8 = '&amp;PARM02'
END ELSE
BEGIN
SELECT PRODDTA.F4801.WADL01 as DESCRIPTION
FROM PRODDTA.F4801 
WHERE PRODDTA.F4801.WADOCO = '&amp;PARM02'
END</SQLText>

Look at using [dynamic SQL](http://www.mssqltips.com/sqlservertip/1160/execute-dynamic-sql-commands-in-sql-server/) — Icemanind, Jun 22 '15 at 23:04
In what way is it not working for you? Aside from having what I assume are parameters being specified as literal text (and thus unable to match for equality), I'd expect this to work. — Ben Thul, Jun 22 '15 at 23:12
Well don't I feel foolish. Using PARM02 where I should have used PARM01. Of course it didn't work. After fixing the typo it works like a charm. — WinkWink, Jun 23 '15 at 00:29

score 0 · Answer 1 · answered Jun 22 '15 at 23:09

You could try using a JOIN statement.

http://www.sqlfiddle.com/#!9/23461d/1

Here is a fiddle showing two tables. The following code snip will give you the values from both tables, using the Key as the matching logic.

SELECT Table1.description, Table1.key, Table2.description
from Table1
Join Table2 on Table1.key = Table2.key

score 0 · Answer 2 · answered Jun 22 '15 at 23:15

Here's one way to do it. If PARM03='Use Table1' then the top half of the union will return records and vice versa. This won't necessarily product good performance though. You should consider why you are storing data in this way. It looks like you are partitioning data across different tables which is a bad idea.

SELECT PRODDTA.TABLE1.T1DESC as DESCRIPTION 
FROM PRODDTA.TABLE1 
WHERE PRODDTA.TABLE1.T1KEY = '&amp;PARM02'
AND &amp;PARM03='Use Table1'
UNION ALL
SELECT PRODDTA.TABLE2.T2DESC as DESCRIPTION 
FROM PRODDTA.TABLE2 
WHERE PRODDTA.TABLE2.T2KEY = '&amp;PARM02'</SQLText>
AND &amp;PARM03='Use Table2'

SQL - How can I return a value from a different table base on a parameter?

2 Answers2