Using results from one query as the parameters for another

Question

Is there a way to have a SQL query automatically update its' parameters based on the results of a previous query?

Here's the scenario I'm trying to achieve:

A report should automatically be run daily that runs the following queries (in order):

1. SELECT id of rows that match a certain criteria (we'll call this result set "X")
2. SELECT certain fields from rows that are titled "Cancelled-[X]"
3. UPDATE rows from [X] with information from "Cancelled-[X]"

Answer 1

Consider using Common Table Expressions (CTEs).

I don't have your table structures nor your queries, but what I would envisage is something like this:

WITH cte1 AS
(
 Perform your 1.SELECT id of rows that match a certain criteria (we'll call this result set "X")`
),
cte2 AS
(
 Perform you 2.SELECT certain fields from rows that are titled "Cancelled-[X]"
)
UPDATE rows from [X] with information from "Cancelled-[X]";

There are some restrictions on CTEs. You can't perform UPDATEs from them for example. I'm not really a MS SQL Server person, but here are a couple of references which should help (1, 2).

For an example of them in action, see my post here. It's a PostgreSQL solution, but the MS SQL Server syntax is virtually identical.

Answer 2

Use local Temp Table in your procedure:

SELECT id INTO #X
FROM ABC WHERE MyCriteria = 'XYZ';
SELECT * FROM ABC 
WHERE Cancelled = 'Yes' and 
 id in (SELECT id FROM #X);
UPDATE ABC SET MyColumn = 'Updated'
WHERE Cancelled = 'Yes' and 
 id in (SELECT id FROM #X);