## [範本] SQL 預存程序(stored procedure) + `ADO.Net`
### 1. 搜尋"平均"年齡(小數點後第一位)
#### 1-1 於 SQL 建立 GetAvgOfPeopleAge 預存程序

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- 
Create PROCEDURE GetAvgOfPeopleAge -- Your Procedure Name
    -- 呼叫 Procedure 要送進來的參數
    @City nvarchar(50)='',
    @Status nvarchar(50)=''
AS
BEGIN
	SET NOCOUNT ON;

	-- 宣告與給予空白初始值 WHERE字串 變數
	DECLARE @WhereClause NVARCHAR(MAX) = '';

	-- 根據條件動態構建 WHERE 子句
	IF @City <> ''
	BEGIN
		SET @WhereClause = @WhereClause + ' AND City = @City'; --最前面有留空白
	END

	IF @Status <> ''
	BEGIN
		SET @WhereClause = @WhereClause + ' AND Status = @Status'; --最前面有留空白
	END


    DECLARE @Query NVARCHAR(MAX) = '';
    SET @Query = '
    BEGIN
        SELECT Round(AVG(FloatD),1) as AGE_Result
    FROM (
        SELECT Round((DATEDIFF(day,CY,getdate()) /365.0),1) as FloatD
        FROM (
        SELECT CASE 
                WHEN SUBSTRING(StartTime, 1, 1) IN (''0'', ''1'') 
                    THEN CONCAT(
                    TRY_CAST(TRY_CAST(SUBSTRING(StartTime, 1, 3) AS int) + 1911 AS VARCHAR(4)),
                    ''-'', 
                    SUBSTRING(StartTime, 4, 2),
                    ''-'', 
                    SUBSTRING(StartTime, 6, 2)
                    )
                ELSE NULL 
            END AS CY
        FROM People
        WHERE 1=1'+@WhereClause+'
        ) as PeopleCY
    ) as Result
    END'

    --EXEC sp_executesql @Query, N'@City NVARCHAR(50), @Status NVARCHAR(50)', @City, @Status;
    EXEC sp_executesql @Query;

END;

- Note：如果要**測試預存程序**，可以在查詢視窗輸入：

EXEC GetAvgOfPeopleAge @City='01', @Status='在職';

#### 1-2 於 MVC5 中建立 GetAvgOfPeopleAge 函式

static string GetAvgOfPeopleAge(string connectionString, string city,string status)
{
	string avgAge = "";
	double ageNum = 0;
	using (SqlConnection conn = new SqlConnection(connectionString))
	{
		using (SqlCommand cmd = new SqlCommand("GetAvgOfPeopleAge", conn))
		{
			cmd.CommandType = CommandType.StoredProcedure;
			cmd.Parameters.AddWithValue("@City", city ?? "");
			cmd.Parameters.AddWithValue("@Status", status ?? "");

			conn.Open();
			using (SqlDataReader reader = cmd.ExecuteReader())
			{
				if (reader.Read())
				{
					
					avgAge = reader[0].ToString();
					if (Double.TryParse(avgAge, out ageNum))
					{
						ageNum = Math.Round(ageNum, 1);
						//Console.WriteLine($"{avgAge}");
					}
					else
					{
						//Console.WriteLine($"無法轉型");
					}
				}
			}
		}
	}

	return ageNum.ToString();
}

#### 1-3 於 MVC5 Controller 內部呼叫中 GetAvgOfPeopleAge 函式

// GetAvgOfPeopleAge 函式參數由 Controller 參數抓取
try
{
    string avgAge = GetAvgOfPeopleAge(ConnectionString, city, status);
    if (avgAge == string.Empty)
    {
        //Console.WriteLine($"The average is null.");
    }
    else
    {
        //Console.WriteLine($"The average age in {city} is {avgAge}.");
    }
}
catch (Exception ex)
{
    Console.WriteLine($"An error occurred: {ex.Message}");
}

---

### 2. 搜尋"最年長"、"最年輕"的人(相同的都要取出)

#### 2-1 於 SQL 建立 GetAgingListLoop 預存程序

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- 
Create PROCEDURE GetAgingListLoop -- Your Procedure Name
    -- 呼叫 Procedure 要送進來的參數
    @City NVARCHAR(50)='',
    @Status NVARCHAR(50)='',
    @OrderInput NVARCHAR(50)='',
    @OrderWay NVARCHAR(50)='',
    @RowNumber NVARCHAR(50)=''
AS
BEGIN
    SET NOCOUNT ON;

    --動態組 Where 查詢字串
    DECLARE @WhereClause NVARCHAR(MAX) = '';
    IF(@City!='')
    BEGIN
        SET @WhereClause=@WhereClause+'AND City=@City '; -- 最後面要有空格
    END
    IF(@Status!='')
    BEGIN
        SET @WhereClause=@WhereClause+'AND Status=@Status ';
    END
    -- 判斷 Input 為何，變動 @OrderWay 變數
    IF(@OrderInput='Old')
    BEGIN
        SET @OrderWay='DESC';
    END
    ELSE IF(@OrderInput='Young')
    BEGIN
        SET @OrderWay='ASC';
    END

    -- 組查詢字串
    DECLARE @Query NVARCHAR(MAX) = ''
    SET @Query='
    WITH CTE AS (
        SELECT [Name], [Status], [StartTime], [City],
            ROW_NUMBER() OVER (ORDER BY StartTime '+@OrderWay+') AS RowNumber
        FROM [testdb].[dbo].[People]
        WHERE 1=1 '+ @WhereClause+'
    )
    SELECT Name,Status,City,
        Round((DATEDIFF(day,CY,getdate()) /365.0),1) as FloatD
    FROM(
            SELECT Name, Status, City,
                CASE 
                    WHEN SUBSTRING(StartTime, 1, 1) IN (''0'', ''1'') 
                    THEN CONCAT(
                        TRY_CAST(TRY_CAST(SUBSTRING(StartTime, 1, 3) AS int) + 1911 AS VARCHAR(4)),
                        ''-'', 
                        SUBSTRING(StartTime, 4, 2),
                        ''-'', 
                        SUBSTRING(StartTime, 6, 2)
                        )
                ELSE NULL
                END AS CY
            FROM CTE
            WHERE RowNumber='+@RowNumber  +'
    ) AS FIRSTQuery
    '
    -- 執行 SQL字串指令(可以額外輸入參數化參數)
    --EXEC sp_executesql @Query,N'@City NVARCHAR(50),@Status NVARCHAR(50),@OrderInput NVARCHAR(50),@RowNumber NVARCHAR(50)',@City,@Status,@OrderInput,@RowNumber
    EXEC sp_executesql @Query;

END

- Note：如果要**測試預存程序**，可以在查詢視窗輸入：

EXEC GetAgingListLoop @City='01', @Status='在職', @OrderInput='Old', @RowNumber='1';

#### 2-2 於 MVC5 中建立 GetAgingPeopleLoop 函式

static List<People> GetAgingPeopleLoop(string connectionString, string city, string status,string orderInput) // string rowNumber 可以不用，內部會宣告，並動態遞增
{
    List<People> PeopleList = new List<People>();
    int start_Row_number = 1;
    string Aging_number = ""; // 最為 年輕 或 年長 的歲數
    try
    {
        while (true)
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = new SqlCommand("GetAgingListLoop", conn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@City", city ?? "");
                    cmd.Parameters.AddWithValue("@Status", status ?? "");
                    cmd.Parameters.AddWithValue("@OrderInput", orderInput ?? "");
                    cmd.Parameters.AddWithValue("@RowNumber", start_Row_number.ToString() ?? "");

                    conn.Open();
                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            if (reader.Read())
                            {

                                if (start_Row_number == 1)
                                {
                                    People people = new People();
                                    people.Name = reader["Name"].ToString();
                                    people.Age = reader["FloatD"].ToString();
                                    people.City = reader["City"].ToString();
                                    people.Status = reader["Status"].ToString();
                                    PeopleList.Add(people);
                                    Aging_number = reader["FloatD"].ToString();
                                    start_Row_number += 1;
                                }
                                else
                                {
                                    if (reader["FloatD"].ToString() != Aging_number)
                                    {
                                        break;
                                    }
                                    else
                                    {
                                        People people = new People();
                                        people.Name = reader["Name"].ToString();
                                        people.Age = reader["FloatD"].ToString();
                                        people.City = reader["City"].ToString();
                                        people.Status = reader["Status"].ToString();
                                        PeopleList.Add(people);
                                        start_Row_number += 1;
                                    }
                                }
                            }
                        }
                        else
                        {
                            break;
                        }
                    }
                }
            }
        }
        return PeopleList;
    }
    catch (Exception ex)
    {
        //Console.WriteLine(ex.Message);
        return PeopleList;
    }
}

#### 2-3 於 MVC5 Controller 內部呼叫中 GetAgingPeopleLoop 函式

// GetAgingPeopleLoop 函式參數由 Controller 參數抓取
List<People> ListofPeople = GetAgingPeopleLoop(ConnectionString, city, status, orderInput);
if (ListofPeople.Count!=0)
{
    Console.WriteLine($"The ListofPeople is not null.");
}
else
{
    Console.WriteLine($"The ListofPeople is null");
}

#### X-1 於 SQL 建立 GetAgingList 預存程序(參考，有錯誤)

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- 
Create PROCEDURE GetAgingList -- Your Procedure Name
    -- 呼叫 Procedure 要送進來的參數
    @City nvarchar(50)='',
    @Status nvarchar(50)='',
    @OrderInput nvarchar(50)='',
    @OrderWay nvarchar(50)=''
AS
BEGIN
    SET NOCOUNT ON;

    --動態組 Where 查詢字串
    DECLARE @WhereClause NVARCHAR(MAX) = '';
    IF(@City!='')
    BEGIN
        SET @WhereClause=@WhereClause+'AND City=@City '; -- 最後面要有空格
    END
    IF(@Status!='')
    BEGIN
        SET @WhereClause=@WhereClause+'AND Status=@Status ';
    END
    -- 判斷 Input 為何，變動 @OrderWay 變數
    IF(@OrderInput='Old')
    BEGIN
        SET @OrderWay='DESC';
    END
    ELSE IF(@OrderInput='Young')
    BEGIN
        SET @OrderWay='ASC';
    END

    -- 組查詢字串
    DECLARE @Query NVARCHAR(MAX) = ''
    SET @Query='
    BEGIN
        SELECT Round(AVG(FloatD),1) as AGE_Result
        FROM (
            SELECT TOP 1 WITH TIES Name,Status,City,Round((DATEDIFF(day,CY,getdate()) /365.0),1) as FloatD
            FROM (
            SELECT
                    Name,
                    Status,
                    City,
                    CASE 
                        WHEN SUBSTRING(StartTime, 1, 1) IN (''0'', ''1'') 
                            THEN CONCAT(
                            TRY_CAST(TRY_CAST(SUBSTRING(StartTime, 1, 3) AS int) + 1911 AS VARCHAR(4)),
                            ''-'', 
                            SUBSTRING(StartTime, 4, 2),
                            ''-'', 
                            SUBSTRING(StartTime, 6, 2)
                            )
                        ELSE NULL 
                    END AS CY
            FROM People
            WHERE 1=1 '+@WhereClause+'
            ORDER BY FloatD '+@OrderWay+'
            ) as PeopleCY
    ) as Result
    END
    '
    -- 執行 SQL字串指令(可以額外輸入參數化參數)
    --EXEC sp_executesql @Query,N'@City nvarchar(50),@Status nvarchar(50),@OrderWay nvarchar(50)', @City, @Status,@OrderWay
    EXEC sp_executesql @Query;

END