存储过程已执行但控制器无法获取其执行结果

Let's break down why your sp_GetVotersByAge stored procedure runs successfully but your controller isn't picking up the results, and fix it step by step.

First, let's recap your stored procedure (I'll fill in the truncated part for clarity):

ALTER PROCEDURE [dbo].[sp_GetVotersByAge] 
    @lokSabhaID bigint = null, 
    @vidhanSabhaID bigint = null, 
    @wardID bigint = null, 
    @year int 
AS 
BEGIN 
    IF (@vidhanSabhaID IS NULL AND @wardID IS NULL) 
    BEGIN 
        SELECT COUNT(voterID) AS 'Age Between 18 and 30' 
        FROM voterList 
        WHERE (@year - DATEPART(YEAR, dateOfBirth)) BETWEEN 18 AND 30 
          AND lokSabhaConstituencyID = @lokSabhaID 

        SELECT COUNT(voterID) AS 'Age Between 30 and 50' 
        FROM voterList 
        WHERE (@year - DATEPART(YEAR, dateOfBirth)) BETWEEN 30 AND 50 
          AND lokSabhaConstituencyID = @lokSabhaID 
        -- Assuming a similar query for 50+ age groups follows
    END
    -- Presumably there are ELSE blocks for when @vidhanSabhaID or @wardID are not NULL
END

Common Causes & Fixes #

1. Your stored procedure returns multiple result sets, but your controller isn't handling them #

Right now, your proc sends back separate SELECT statements for each age bracket. Most data access layers (like ADO.NET, Entity Framework) don't automatically pick up multiple result sets unless you explicitly code for it.

Fix 1: Combine results into a single result set (easiest for controllers)
Instead of multiple SELECT calls, return all age groups in one row. This makes consumption straightforward:

ALTER PROCEDURE [dbo].[sp_GetVotersByAge] 
    @lokSabhaID bigint = null, 
    @vidhanSabhaID bigint = null, 
    @wardID bigint = null, 
    @year int 
AS 
BEGIN 
    IF (@vidhanSabhaID IS NULL AND @wardID IS NULL) 
    BEGIN 
        SELECT 
            COUNT(CASE WHEN (@year - DATEPART(YEAR, dateOfBirth)) BETWEEN 18 AND 30 THEN voterID END) AS AgeBetween18And30,
            COUNT(CASE WHEN (@year - DATEPART(YEAR, dateOfBirth)) BETWEEN 30 AND 50 THEN voterID END) AS AgeBetween30And50,
            COUNT(CASE WHEN (@year - DATEPART(YEAR, dateOfBirth)) > 50 THEN voterID END) AS AgeAbove50
        FROM voterList 
        WHERE lokSabhaConstituencyID = @lokSabhaID 
    END
    -- Add similar combined queries for other parameter scenarios
END

Fix 2: Handle multiple result sets in your controller
If you need to keep separate result sets, adjust your controller code to read each one explicitly. For example, in ADO.NET:

using (var connection = new SqlConnection("your_connection_string"))
{
    var command = new SqlCommand("sp_GetVotersByAge", connection);
    command.CommandType = CommandType.StoredProcedure;
    // Add all required parameters here (e.g., command.Parameters.AddWithValue("@year", 2024))
    connection.Open();
    var reader = command.ExecuteReader();
    
    // Read first result set (18-30)
    if (reader.Read())
    {
        var count18To30 = reader.GetInt32(0);
        // Process this value
    }
    reader.NextResult(); // Move to second result set (30-50)
    if (reader.Read())
    {
        var count30To50 = reader.GetInt32(0);
        // Process this value
    }
}

2. Parameter issues are filtering out all results #

• Double-check that @lokSabhaID is being passed correctly from the controller. If it's NULL but your voterList table has non-NULL lokSabhaConstituencyID values, your query will return empty results.
• Fix your age calculation logic: @year - DATEPART(YEAR, dateOfBirth) doesn't account for birthdays that haven't occurred yet in the given year. For precise age calculation, use:

  DATEDIFF(YEAR, dateOfBirth, DATEFROMPARTS(@year, 12, 31))
  

  This calculates age as of December 31st of the target year, avoiding undercounting.

3. Controller isn't executing the proc correctly #

Make sure your controller code:

• Sets the command type to StoredProcedure (not Text).
• Includes all required parameters (especially @year, which has no default value).
• Actually reads the results (don't just call ExecuteNonQuery() if you need to retrieve data—use ExecuteReader() or ExecuteScalar() as needed).

4. Permissions or connection quirks #

Even if the proc runs, if the controller's database user lacks read permissions on the voterList table, it might return empty results without throwing an error. Verify the user has SELECT access to both the table and the stored procedure.

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