如何为PersonInformation表中所有活跃人员执行SpStats存储过程

Got it, let's tackle this problem step by step. You need to execute the SpStats stored procedure for every active person in your PersonInformation table and collect the resulting stat_a and stat_b values. Below are three practical approaches, depending on your SQL Server version and whether you can modify existing objects:

Approach 1: Use a Cursor (Compatible with All SQL Server Versions) #

This is the most straightforward method if you need compatibility with older SQL Server versions, or if you can't modify the SpStats procedure. We'll iterate over each active PersonID, run the procedure, and store results in a temporary table.

-- Create a temporary table to store results
CREATE TABLE #PersonStats (
    PersonID INT,
    stat_a DECIMAL(18,2), -- Match the data type returned by SpStats
    stat_b INT -- Match the data type returned by SpStats
)

-- Declare variables
DECLARE @PersonID INT

-- Declare cursor for active persons (adjust the active filter to match your definition)
DECLARE activePersons CURSOR FOR
SELECT PersonID
FROM PersonInformation
WHERE IsActive = 1 -- Example: use LastLoginDate > DATEADD(month, -6, GETDATE()) if needed

-- Open and iterate through the cursor
OPEN activePersons
FETCH NEXT FROM activePersons INTO @PersonID

WHILE @@FETCH_STATUS = 0
BEGIN
    -- Execute the stored procedure and insert results into temp table
    INSERT INTO #PersonStats (stat_a, stat_b)
    EXEC SpStats @PersonID = @PersonID

    -- Update the PersonID in the temp table (since SpStats doesn't return it)
    UPDATE #PersonStats
    SET PersonID = @PersonID
    WHERE PersonID IS NULL

    FETCH NEXT FROM activePersons INTO @PersonID
END

-- Clean up cursor resources
CLOSE activePersons
DEALLOCATE activePersons

-- View the final aggregated results
SELECT * FROM #PersonStats

-- Drop the temp table when finished
DROP TABLE #PersonStats

Approach 2: Convert SpStats to a Table-Valued Function (Best Performance) #

If you have permission to modify the stored procedure, converting it to a table-valued function (TVF) is the most efficient approach. TVFs support set-based operations, which SQL Server optimizes far better than cursors.

First, rewrite SpStats as an inline TVF:

CREATE FUNCTION dbo.fn_GetPersonStats(@PersonID INT)
RETURNS TABLE
AS
RETURN (
    -- Copy the exact calculation logic from SpStats here
    SELECT 
        stat_a = /* Your existing stat_a calculation */,
        stat_b = /* Your existing stat_b calculation */
    FROM /* Underlying tables used in SpStats */
    WHERE PersonID = @PersonID
)

Then, query it directly with your active persons:

SELECT 
    p.PersonID,
    s.stat_a,
    s.stat_b
FROM PersonInformation p
CROSS APPLY dbo.fn_GetPersonStats(p.PersonID) s
WHERE p.IsActive = 1 -- Adjust active filter to match your needs

This method eliminates iterative processing entirely and is ideal for large datasets.

Approach 3: Use Dynamic SQL with INSERT...EXEC (SQL Server 2005+) #

If you want to avoid cursors but can't modify SpStats, you can generate dynamic SQL to run the procedure for all active IDs in one batch. This works best for smaller datasets.

CREATE TABLE #PersonStats (
    PersonID INT,
    stat_a DECIMAL(18,2),
    stat_b INT
)

DECLARE @SQL NVARCHAR(MAX) = ''

-- Build dynamic SQL for each active PersonID
SELECT @SQL += '
INSERT INTO #PersonStats (stat_a, stat_b)
EXEC SpStats @PersonID = ' + CAST(PersonID AS NVARCHAR(10)) + ';
UPDATE #PersonStats SET PersonID = ' + CAST(PersonID AS NVARCHAR(10)) + ' WHERE PersonID IS NULL;'
FROM PersonInformation
WHERE IsActive = 1

-- Execute the generated dynamic SQL
EXEC sp_executesql @SQL

-- View the final results
SELECT * FROM #PersonStats

DROP TABLE #PersonStats

Note: This method carries minimal SQL injection risk here since PersonID is an integer, but always use parameterized queries if working with string inputs.

内容的提问来源于stack exchange，提问作者nyilmaz