1

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.

Deeptechtons
  • 10,945
  • 27
  • 96
  • 178

2 Answers2

1

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
Mitch Wheat
  • 295,962
  • 43
  • 465
  • 541
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

Oleg Dok
  • 21,109
  • 4
  • 45
  • 54
  • what certain conditions? can u add a few – Deeptechtons Jan 19 '12 at 09:13
  • If you'll use it just one time or amount of rows relatively bit - better create temp table with indexes instead of table variable. Sql Server does not creates statistics on table variables and does it ot temp tables - so, temp tables may outperform the table vars – Oleg Dok Jan 19 '12 at 09:19