SELECT SQL table retrieved by an IF statement

Question

Hella all,

What I want to do is something like that, I will have an SQL table depending on my parameter,

DECLARE @find varchar(30)
SET @find = 'no'

SELECT * FROM
(

    if @find = 'yes'
    (
    SELECT * FROM myTable
    WHERE ID= '5882'
    )
    ELSE
    (
    SELECT * FROM myTable
    WHERE OLD_ID= '5882'
    )   

) X

This is just a simple table that I gave as an example, my real sql query is much more bigger and so I don't think I can use dynamic sql within this query. So I need some other way.

Answer 1

You can use EXEC to execute a SQL string. You have to mind quotes.

DECLARE @column varchar(max);
SET @column = 'OLD_ID'

EXEC('SELECT * FROM myTable WHERE ' + @column + ' = ''5882''')

Answer 2

Use the 'searched CASE function' like this:

WHERE
    CASE 
         WHEN @find = 'no' THEN ID
         WHEN @find = 'yes' THEN OLD_ID
         -- put an ELSE clause here
         -- if you want to catch @find not being no or yes
    END 
    = '5882'

Answer 3

To ensure optimal execution plans, I'd recommend creating separate queries, and calling the appropriate one depending on "@find". e.g. SPROC1 would query on ID SPROC2 would query on OLD_ID

Then either, change your calling code to call the appropriate sproc, OR, if you just want to pass @find in as a parameter, create a 3rd sproc that just acts as a redirect: SPROC3:

IF (@find = 'no')
    EXECUTE SPROC1
ELSE IF (@find = 'yes')
    EXECUTE SPROC2
ELSE
    ....

The risk with other approaches is execution plan pollution whereby a plan gets created for one path (e.g. @find='no') and then when a subsequent call comes in with @find='yes' it ends up using the far less appropriate execution plan, resulting in poor performance. In other words, instead of using an index on ID it may end up using the index on OLD_ID which is obviously not ideal (and vice versa).