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):
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.
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);
