CODEWITHSUNDEEP
sqlt-sqlsql-server-2008join
McVey
McVey

Reputation: 647

Joining on one of Two Tables Based on Parameter

Not sure if this can be done, but here is what I am trying to do.

I have two tables: Table 1 is called Task and it contains all of the possible Task Names Table 2 is called Task_subset and it contains only a subset of the Task Names included in Table 1

I have a variable called @TaskControl, that is passed in as a parameter, it either is equal to Table1 or Table2

Based on the value of the @TaskControl variable I want to join one of my Task Tables

For example:

If @TaskControl = 'Table1':
Select * From Orders O Join Task T on T.id = O.id

If @TaskControl = 'Table2):
Select * From Orders O Join Task_subset T on T.id = O.id

How would I do this, Sql Server 08

Upvotes: 1

Views: 3970

Answers (5)

Nicholas Carey
Nicholas Carey

Reputation: 74345

Try the following. It should avoid the stored procedure plan getting bound based on the value of the parameter passed during the first execution of the stored procedure (See SQL Server Parameter Sniffing for details):

create proc dbo.foo

  @TaskControl varchar(32)

as

  declare @selection varchar(32)
  set @selection = @TaskControl

    select *
    from dbo.Orders t
    join dbo.Task   t1 on t1.id = t.id
    where @selection = 'Table1'
  UNION ALL  
    select *
    from dbo.Orders      t
    join dbo.Task_subset t1 on t1.id = t.id
    where @selection = 'Table2'

  return 0
go

The stored procedure shouldn't get recompiled for each invocation, either, as @Martin suggested might happen, but the parameter value 1st passed in should not influence the execution plan the gets bound. But if performance is an issue, run a sql trace with the profiler and see if the cached execution plan is reused or if a recompile is triggered.

One thing, though: you will need to ensure, though, that each individual select in the UNION returns the exact same columns. Each select in a UNION must have the same number of columns and each column must have a common type (or default conversion to the common type). The 1st select defines the number, types and names of the columns in the result set.

Upvotes: 0

Martin Smith
Martin Smith

Reputation: 453786

If they are UNION compatible you could give this a shot. From a quick test this end it only appears to access the relevant table.

I do agree more with JNK's and Remus's answers however. This does have a recompilation cost for every invocation and not much benefit.

;WITH T AS
(
SELECT  'Table1' AS TaskControl, id 
FROM Task
UNION ALL
SELECT  'Table2' AS TaskControl, id 
FROM Task_subset
)
SELECT *
FROM T
 JOIN Orders O on T.id = O.id 
WHERE TaskControl = @TaskControl
OPTION (RECOMPILE)

Upvotes: 1

Remus Rusanu
Remus Rusanu

Reputation: 294407

Doing it exactly as you do it right now is the best way. Having one single statement that attempts to somehow dynamically join one of two statements is the last thing you want. T-SQL is a language for data access, not for DRY code-reuse programming. If you attempt to have a single statement then the optimizer has to come up with a plan that always work, no matter the value of @TaskControl, and so the plan will always have to join both tables.

A more lengthy discussion on this topic is Dynamic Search Conditions in T-SQL (your dynamic join falls into the same topic as dynamic search).

Upvotes: 2

JNK
JNK

Reputation: 65197

Don't overcomplicate it. Put it into a stored proc like so:

CREATE PROCEDURE dbo.MyProcedure(@TaskControl varchar(20))
AS

If @TaskControl = 'Table1'
    Select * From Orders O Join Task T on T.id = O.id

ELSE If @TaskControl = 'Table2'
    Select * From Orders O Join Task_subset T on T.id = O.id

ELSE SELECT 'Invalid Parameter'

Or just straight TSQL with no proc:

If @TaskControl = 'Table1'
    Select * From Orders O Join Task T on T.id = O.id

ELSE If @TaskControl = 'Table2'
    Select * From Orders O Join Task_subset T on T.id = O.id

Upvotes: 3

Tom H
Tom H

Reputation: 47402

I don't know how good performance would be, and this would not scale well as you add on additional optional tables, but this should work in the situation that you present.

SELECT
    O.some_column,
    COALESCE(T.some_task_column, TS.some_task_subset_column)
FROM
    Orders O
LEFT OUTER JOIN Tasks T ON
    @task_control = 'Tasks' AND
    T.id = O.id
LEFT OUTER JOIN Task_Subsets TS ON
    @task_control = 'Task Subsets' AND
    TS.id = O.id

Upvotes: 0

Related Questions