May 23, 2008 at 8:31 am
Hi, Is it possible to create a simple CTE within a stored proc and reference the CTE multiple times within separate INSERT, UPDATE and DELETE statements without having to redefine CTE before each DML statement?
May 23, 2008 at 8:33 am
In short - no. CTE's are designed to only apply to the one statement.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
May 23, 2008 at 8:36 am
You can "cheat" if OUTPUT operator will do.
N 56°04'39.16"
E 12°55'05.25"
May 23, 2008 at 8:37 am
Peso (5/23/2008)
You can "cheat" if OUTPUT operator will do.
And that's the long version:)
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
May 23, 2008 at 8:41 am
Well... At least OP can find the answer "Huh?" and actually pick up Books Online and read about OUTPUT.
N 56°04'39.16"
E 12°55'05.25"
May 23, 2008 at 8:45 am
it might sound a silly question but how could OUTPUT be used in this context?
May 23, 2008 at 8:48 am
Peso (5/23/2008)
Well... At least OP can find the answer "Huh?" and actually pick up Books Online and read about OUTPUT.
Oh - agreed. I wasn't taking a shot at your answer: it was just the "long version" of the "in short" answer I gave (i.e. the exception to how you can't get CTE results to survive). Plenty enough info provided for them to go find what they need.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
May 23, 2008 at 8:50 am
Yes you can invoke same CTE several times in immediate statement after CTE definition.
SELECT *
FROM cte AS v1
INNER JOIN cte AS v2 ON v2.pk = v1.pk
WHERE v1.Col2 = 'Peso' AND v2.Col3 IS NULL
OUTPUT operator makes it possible to do a DELETE and INSERT in same statement.
Also UPDATE/INSERT and INSERT/INSERT.
See http://weblogs.sqlteam.com/peterl/archive/2007/10/03/New-OUTPUT-operator.aspx
N 56°04'39.16"
E 12°55'05.25"
May 23, 2008 at 8:58 am
Stuart (5/23/2008)
it might sound a silly question but how could OUTPUT be used in this context?
The point of OUTPUT is to capture the results from an INSERT/UPDATE/DELETE statement and use that for other items. So - if you wanted to make an update to records you juse inserted, you could use the OUTPUT clause to capture which rows they were (for example capture the keys for the rows you just inserted), giving you a much faster way back to those rows, or a clean way to get info to reuse elsewhere.
Think a scenario where you might be inserting hierarchical data (parent rows, then child rows, then grandchild rows, etc....). Using the OUTPUT would allow you to retrieve identity keys (should you need them) for the parent rows you just inserted, giving you quick access to what would then be the foreign key values in the child level. etc...
It's not an incredibly widely applicable solution, since it would be constrained to rows/columns used in the DML statement you're outputting. But if it does apply - might help the process more efficient.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply