Reusing SELECT query results in a stored procedure

Question

This is probably a very simple question, but my attempts to find the answer prevented Google from finding answers that showed how to reuse the request, creating a stored procedure instead. I want to reuse the query results inside a stored procedure.

Here is an example of cutting, where I cut NOCOUNT, XACT_ABORT, TRANSACTION, TRYand most of the logic.

CREATE PROCEDURE Do_Something
    @userId UNIQUEIDENTIFIER
AS
BEGIN

    DELETE FROM LikedItems
    WHERE likedItemId IN
    (
        SELECT Items.id FROM Items
        WHERE Items.userId = @userId
    )

    DELETE FROM FollowedItems
    WHERE followedItemId IN
    (
        SELECT Items.id FROM Items
        WHERE Items.userId = @userId
    )

END

What is the syntax for reusing duplicate nested results SELECTinstead of doing it twice?

Answer 1

SELECT , , . .

Temp

CREATE PROCEDURE Do_Something
    @userId UNIQUEIDENTIFIER
AS
BEGIN

    CREATE TABLE #Temp(id int);

    INSERT INTO #Temp(id)
    SELECT Items.id 
    FROM Items
    WHERE Items.userId = @userId;

    DELETE FROM LikedItems
    WHERE likedItemId IN
    (
        SELECT id FROM #Temp
    )

    DELETE FROM FollowedItems
    WHERE followedItemId IN
    (
        SELECT id FROM #Temp
    )

    DROP TABLE #Temp;

END

CREATE PROCEDURE Do_Something
    @userId UNIQUEIDENTIFIER
AS
BEGIN

    DECLARE @Temp TABLE(id int);

    INSERT INTO @Temp(id)
    SELECT Items.id 
    FROM Items
    WHERE Items.userId = @userId;

    DELETE FROM LikedItems
    WHERE likedItemId IN
    (
        SELECT id FROM @Temp
    )

    DELETE FROM FollowedItems
    WHERE followedItemId IN
    (
        SELECT id FROM @Temp
    )

END

Answer 2

, .

CREATE PROCEDURE Do_Something
@userId UNIQUEIDENTIFIER
AS
BEGIN

DECLARE @TempItems TABLE (id int)
INSERT INTO @TempItems
SELECT Items.id FROM Items
WHERE Items.userId = @userId

DELETE FROM LikedItems
WHERE likedItemId IN
(
    SELECT id FROM @TempItems 
)

DELETE FROM FollowedItems
WHERE followedItemId IN
(
    SELECT id FROM @TempItems 
)

END

Answer 3

- . ( ) , . - , .

, -: -. , - , .

, . Items , SERIALIZABLE. , . , ​​SERVER . - , . "" " " . , - , ( - ) / , .