CODEWITHSUNDEEP
sql-servert-sqlstored-procedures
pieDragon
pieDragon

Reputation: 7

Use a stored procedure in a SQL query

I need to use some data that comes from a stored procedure and I want to put that data into a select statement. I would use a function but I am not allowed to and whoever set up this database decided to put this as a stored procedure. It returns 1 value a count in a table.

The stored procedure takes in a guid and then gets the count for that guid item. The logic for this count is complicated enough I don't want to put it into a sub query. I don't care if it's really inefficient as hopefully I will only need to run this once. I am also using SSMS If that matters.

My current idea is to do the following:

DECLARE @TempResult TABLE 
(
    ItemID UNIQUEIDENTIFIER,
    Quantity INT
);

-- Step 2: Declare a cursor to iterate through all ItemModelIDs
DECLARE @ItemID UNIQUEIDENTIFIER;
DECLARE @Quantity INT;

DECLARE ItemCursor CURSOR FOR
    SELECT IDItemModel FROM Items

OPEN ItemCursor;

FETCH NEXT FROM ItemCursor INTO @ItemModelID;

-- Step 3: Loop through each ItemModelID and execute the stored procedure
WHILE @@FETCH_STATUS = 0
BEGIN
    -- Execute the stored procedure for the current ItemModelID
    EXEC @Quantity = dbo.[Stored Proc] @ItemModelID;

    INSERT INTO @TempResult (ItemID, Quantity) 
    VALUES (@ItemID, @Quantity)
END;

CLOSE ItemCursor;
DEALLOCATE ItemCursor;

The output of the stored procedure for one of the items:

Count
1

Upvotes: 0

Views: 83

Answers (1)

Charlieface
Charlieface

Reputation: 71076

You are missing a FETCH NEXT inside the loop. It also appears that the procedure does a SELECT not a RETURN, so you need INSERT ... EXEC ...

To avoid repating yourself, you can put the WHILE check inside the loop using BREAK. Also if you use a local variable to store the cursor then you don't need to close or deallocate it.

DECLARE @TempResult TABLE 
(
    ItemID UNIQUEIDENTIFIER,
    Quantity INT
);

DECLARE @ItemID UNIQUEIDENTIFIER;
DECLARE @ItemCursor CURSOR;

SET @ItemCursor = CURSOR FORWARD_ONLY KEYSET FOR
    SELECT IDItemModel
    FROM Items;

OPEN @ItemCursor;

WHILE 1=1
BEGIN
    FETCH NEXT FROM ItemCursor
      INTO @ItemModelID;

    IF @@FETCH_STATUS <> 0
        BREAK;

    INSERT @TempResult (ItemID, Quantity)
    EXEC dbo.[Stored Proc] @ItemID;
END;

I strongly recommend you instead rewrite this whole procedure to incorporate the inner procedure, it's almost certainly more performant.

Upvotes: 1

Related Questions