-- if no parameter values passed, then only query table variable
	IF @performanceCriteriaID IS NULL AND @businessObjectiveID IS NULL
		BEGIN -- search all user answers without querying many-to-many tables
			SELECT 
				*
			FROM 
				@tmp 
			ORDER BY 
				surname, 
				name
		END
	-- otherwise, query the many-to-many tables using the passed variables
	ELSE
		BEGIN -- query many-to-many table(s)
			SELECT
				DISTINCT(tmp.answerID),
				tmp.*
			FROM
				@tmp tmp LEFT OUTER JOIN
				[tbl_BusinessObjectives] bo ON bo.uiAnswerID = tmp.answerID LEFT OUTER JOIN -- only when needed?
				[tbl_PerformanceCriteria] pc ON pc.uiAnswerID = tmp.answerID -- only when needed?
			WHERE
				(bo.boID = @businessObjectiveID OR @businessObjectiveID IS NULL) AND
				(pc.pcID = @performanceCriteriaID OR @performanceCriteriaID IS NULL)
			ORDER BY
				tmp.surname,
				tmp.name
		END

INNER JOIN [tbl_BusinessObjectives] bo ON bo.uiAnswerID = tmp.answerID and bo.boID = @businessObjectiveID
where isnull(@businessObjectiveID, 0) > 0

quote:

---

I have three tables; one is a table variable, and the other two are many-to-many tables. My query always searches @tmp, but I was wondering if it is possible to only query the other two tables when a variable is passed to query them with. Here's what I'm doing so far which should hopefully explain what I mean...



-- if no parameter values passed, then only query table variable
IF @performanceCriteriaID IS NULL AND @businessObjectiveID IS NULL
BEGIN -- search all user answers without querying many-to-many tables
SELECT
*
FROM
@tmp
ORDER BY
surname,
name
END
-- otherwise, query the many-to-many tables using the passed variables
ELSE
BEGIN -- query many-to-many table(s)

SELECT
DISTINCT(tmp.answerID),
tmp.*
FROM
@tmp tmp LEFT OUTER JOIN
[tbl_BusinessObjectives] bo ON bo.uiAnswerID = tmp.answerID LEFT OUTER JOIN -- only when needed?
[tbl_PerformanceCriteria] pc ON pc.uiAnswerID = tmp.answerID -- only when needed?
WHERE
(bo.boID = @businessObjectiveID OR @businessObjectiveID IS NULL) AND
(pc.pcID = @performanceCriteriaID OR @performanceCriteriaID IS NULL)
ORDER BY
tmp.surname,
tmp.name

END



So here's the pseudo code I'm aiming for...

1. If @performanceCriteriaID and @businessObjectiveID are null, just query @tmp
2. If @performanceCriteriaID is not null, return rows from @tmp where a related row(s) exists in [tbl_BusinessObjectives]
3. If @businessObjectiveID is not null, return rows from @tmp where a related row(s) exists in [tbl_PerformanceCriteria]
4. If 2+3 are both not null, return rows from @tmp where a related row(s) exists in [tbl_BusinessObjectives] and [tbl_PerformanceCriteria]

So I guess I'm trying to join only when required. I only return row values from @tmp to prevent duplicate @tmp rows showing when multiple related rows exist in the other table(s). Or am I wasting my time?!?

---

• If the @boID and @pcID values are null, then simply select all from @tmp


• If @boID is not null, then show all rows from @tmp where a related row exists in @businessObjectives


• If @pcID is not null, then show all rows from @tmp where a related row exists in @performanceCriteria


• if @boID is not null and @pcID is not null, then only show rows from @tmp where related rows exist in both @businessObjectives and @performanceCriteria

declare @tmp table
	(
	answerID int identity(1,1),
	answerText nvarchar(max)
	)

declare @businessObjectives table
	(
	ID int identity(1,1),
	answerID int not null,
	boID int not null
	)

declare @performanceCriteria table
	(
	ID int identity(1,1),
	answerID int not null,
	pcID int not null
	)

declare @i int
declare @lastID int
SET @i = 1
WHILE @i < 500
	BEGIN
		INSERT INTO @tmp (answerText) VALUES ('Placeholder text...') 
		SET @lastID = SCOPE_IDENTITY()
		INSERT INTO @businessObjectives (answerID, boID) VALUES (@lastID, (SELECT CAST(RAND() * 10 AS INT)))
		INSERT INTO @businessObjectives (answerID, boID) VALUES (@lastID, (SELECT CAST(RAND() * 10 AS INT)))
		INSERT INTO @performanceCriteria (answerID, pcID) VALUES (@lastID, (SELECT CAST(RAND() * 10 AS INT)))
		INSERT INTO @performanceCriteria (answerID, pcID) VALUES (@lastID, (SELECT CAST(RAND() * 10 AS INT)))
		SET @i = @i + 1
	END
	
-- perform selects -----------------------------
declare @boID int
declare @pcID int
SET @boID = 5
--SET @pcID = 3

IF @boID IS NULL AND @pcID IS NULL 
	BEGIN
		SELECT * FROM @tmp ORDER BY answerID
	END
ELSE
	BEGIN
		SELECT
			DISTINCT(tmp.answerID),
			tmp.*,
			bo.boID,
			pc.pcID
		FROM
			@tmp tmp LEFT OUTER JOIN
			@businessObjectives bo ON bo.answerID = tmp.answerID LEFT OUTER JOIN -- Unnecessary join when @boID IS NULL!
			@performanceCriteria pc ON pc.answerID = tmp.answerID -- Unnecessary join when @pcID IS NULL!
		WHERE
			(bo.boID = @boID OR @boID IS NULL) AND
			(pc.pcID = @pcID OR @pcID IS NULL)
		ORDER BY
			tmp.answerID 	
	END