CODEWITHSUNDEEP
sqlsql-servert-sqlstored-procedures
dumbledad
dumbledad

Reputation: 17527

Reuse results of SELECT query inside a stored procedure

This is probably a very simple question, but my attempts to search for an answer are thwarted by Google finding answers showing how to reuse a query by making a stored procedure instead. I want to reuse the results of a query inside a stored procedure.

Here's a cut-down example where I've chopped out NOCOUNT, XACT_ABORT, TRANSACTION, TRY, and much 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 to reuse the results of the duplicated nested SELECT rather than doing it twice?

Upvotes: 4

Views: 3110

Answers (3)

IVNSTN
IVNSTN

Reputation: 9299

If the subquery is fast and simple - no need to change anything. Item's data is in the cache (if it was not) after the first query, locks are obtained. If the subquery is slow and complicated - store it into a table variable and reuse by the same subquery as listed in the question.

If your question is not related to performance and you are beware of copy-paste: there is no copy-paste. There is the same logic, similar structure and references - yes, you will have almost the same query source code.

In general, it is not the same. Some rows could be deleted from or inserted into Items table after the first query unless your are running under SERIALIZABLE isolation level. Many different things could happen during first delete, between first and second delete statements. Each delete statement also requires it's own execution plan - thus all the information about tables affected and joins must be provided to SERVER anyway. You need to filter by the same source again - yes, you provide subquery with the same source again. There is no "twice" or "reuse" of a partial code. Data collected by a complicated query - yes, it can be reused (without running the same complicated query - by simple querying from prepared source) via temp tables/table variables as mentioned before.

Upvotes: 1

Vladimir Baranov
Vladimir Baranov

Reputation: 32685

You can INSERT result of the SELECT into a temporary table or table variable, but it doesn't automatically mean that the overall performance would be better. You need to measure it.

Temp Table

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

Table variable

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

Upvotes: 3

benni_mac_b
benni_mac_b

Reputation: 8877

You can declare a table variable to store the results of the select and then simply query that.

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

Upvotes: 1

Related Questions