(1) FROM / JOIN -- 產生虛擬表 VT1
(2) ON -- 篩選 JOIN 條件 → VT2
(3) OUTER JOIN -- 加回外部列 → VT3
(4) WHERE -- 列篩選 → VT4
(5) GROUP BY -- 分組 → VT5
(6) CUBE | ROLLUP -- 超級群組 → VT6
(7) HAVING -- 群組篩選 → VT7
(8) SELECT -- 投影 → VT8
(9) DISTINCT -- 去重 → VT9
(10) ORDER BY -- 排序 → VC10 (游標)
(11) TOP / OFFSET-FETCH -- 取前 N 列 → VT11 (最終結果)
啟示:WHERE 在 GROUP BY 前執行,故無法用別名;SELECT 最後才投影,故 ORDER BY 可用別名。
USE [DB]; GO
SET ANSI_NULLS ON; GO
SET QUOTED_IDENTIFIER ON; GO
CREATE PROCEDURE [dbo].[UP_InsertData]
@CustomerName VARCHAR(50)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @Now DATETIME = GETDATE();
BEGIN TRY
-- 業務 SQL 寫這裡
INSERT INTO Customers (Name, CreateTime) VALUES (@CustomerName, @Now);
END TRY
BEGIN CATCH
DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE(),
@ErrorSeverity INT = ERROR_SEVERITY(),
@ErrorState INT = ERROR_STATE();
RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);
RETURN -1;
END CATCH
END
CREATE PROCEDURE [dbo].[UP_InsertWithTx]
@CustomerName VARCHAR(50)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @Now DATETIME = GETDATE();
BEGIN TRY
BEGIN TRANSACTION myTrans;
-- 業務 SQL
INSERT INTO Customers (Name, CreateTime) VALUES (@CustomerName, @Now);
-- 可多條 DML
COMMIT TRANSACTION myTrans;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION myTrans;
DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE(),
@ErrorSeverity INT = ERROR_SEVERITY(),
@ErrorState INT = ERROR_STATE();
RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);
END CATCH
END
SELECT * FROM (
SELECT
ROW_NUMBER() OVER (PARTITION BY ProductNO ORDER BY ProductNO) AS RowNum,
*
FROM IM.dbo.ItemInfo
) t
WHERE t.RowNum IN (1, 2, 3); -- 每組前 3 筆
CREATE TYPE [dbo].[GCRP_PendingGiftCard_TYPE] AS TABLE (
[RowNum] INT NULL,
[GiftCardNO] VARCHAR(500) NULL,
[UsedDate] DATETIME NULL,
[CustomerName] VARCHAR(50) NULL,
[ReduceAmount] DECIMAL(18,2) NULL,
[Amount] DECIMAL(18,2) NULL
);
// C# 範例
var dt = new DataTable();
dt.Columns.Add("RowNum", typeof(int));
dt.Columns.Add("GiftCardNO", typeof(string));
// ... 填充資料
using (var cmd = new SqlCommand("UP_ProcessGiftCards", conn)) {
cmd.CommandType = CommandType.StoredProcedure;
var param = cmd.Parameters.AddWithValue("@TVP", dt);
param.SqlDbType = SqlDbType.Structured;
param.TypeName = "dbo.GCRP_PendingGiftCard_TYPE";
cmd.ExecuteNonQuery();
}
CREATE PROCEDURE UP_ProcessGiftCards
@TVP GCRP_PendingGiftCard_TYPE READONLY
AS
BEGIN
SELECT * FROM @TVP; -- 如同操作表變數
-- 可 INSERT INTO TargetTable SELECT * FROM @TVP;
END
READONLY 關鍵字強制 (TVP 僅輸入)掌握執行順序優化查詢、標準化程序模板保證品質、善用窗口函數解決 Top-N、採用 TVP 高效傳遞批次資料,是 T-SQL 開發進階的四大基石。