SQL Server 2005 - DataType of variable to store a RowSet

Question

In a typical stored procedure i am working there are various checks with the same query as below.

SELECT ... FROM Projects WHERE ProjectId IN (SELECT ProjectId FROM Tasks)

i would like to replace the query (SELECT ProjectId FROM Tasks) with a variable but am confused as to what datatype it has to be. Do you know? also caching this result is detrimental or is there easier way to doing this.

Answer 1

You may cteate table typed variable and use it multiple times, like that:

CREATE @Projects TABLE(Id INT NOT NULL)

INSERT @Projects SELECT ProjectId FROM Tasks

SELECT ... FROM Projects WHERE ProjectId IN (SELECT ProjectId FROM @Projects)
SELECT ... FROM Projects WHERE ProjectId NOT IN (SELECT ProjectId FROM @Projects)

Althought - it is not replacement of query by variable, it is make more reusable the subquery results

But

under certain conditions this may degrade your queries' performance

Answer 2

This form is preferable (although optimiser should perform this optimisation):

SELECT ... FROM Projects p
INNER JOIN Tasks t ON t.ProjectID = p.ProjectId

Then add the condition as a WHERE clause (instead of performing the inner select and attempting to place a list of ProjectId's in a variable):

SELECT ... FROM Projects p
INNER JOIN Tasks t ON t.ProjectID = p.ProjectId
WHERE someCondtionOnTasksTable