SQL Server 呼叫預存程序完整指南：無輸出參數、有輸出參數與查詢欄位所屬表

一、呼叫無輸出參數的預存程序

-- 定義
CREATE PROCEDURE sys.sp_AddProduct
  @ViewCount INT = 0,
  @HotCount  INT = 0
AS
BEGIN
  -- 業務邏輯
  PRINT 'ViewCount=' + CAST(@ViewCount AS VARCHAR) + ', HotCount=' + CAST(@HotCount AS VARCHAR);
END;
GO

-- 呼叫
DECLARE @ViewCount INT = 10, @HotCount INT = 5;
EXEC sys.sp_AddProduct @ViewCount, @HotCount;  -- 依位置對應
-- 或
EXEC sys.sp_AddProduct @ViewCount = @ViewCount, @HotCount = @HotCount;  -- 依名稱對應 (推薦)

二、呼叫有輸出參數的預存程序

-- 定義
CREATE PROCEDURE sys.sp_AddProduct
  @ViewCount INT = 0,
  @HotCount  INT OUTPUT  -- 標記為輸出
AS
BEGIN
  SET @HotCount = @HotCount + 10;  -- 修改輸出值
  PRINT 'New HotCount=' + CAST(@HotCount AS VARCHAR);
END;
GO

-- 呼叫：必須在 EXEC 時加 OUTPUT 關鍵字
DECLARE @ViewCount INT = 10, @HotCount INT = 5;
EXEC sys.sp_AddProduct @ViewCount, @HotCount OUTPUT;
PRINT 'Returned HotCount=' + CAST(@HotCount AS VARCHAR);  -- 顯示 15

關鍵：定義用 OUTPUT，呼叫同樣要加 OUTPUT，否則外部變數不會接收回傳值。

三、查詢某欄位存在於哪些表中

利用系統目錄 sys.columns + sys.tables (或舊版 sysobjects/syscolumns)：

-- 現代寫法 (SQL 2005+)
SELECT 
  OBJECT_SCHEMA_NAME(t.object_id) AS SchemaName,
  t.name AS TableName,
  c.name AS ColumnName
FROM sys.columns c
JOIN sys.tables t ON c.object_id = t.object_id
WHERE c.name LIKE '%m_Id%'  -- 部分比對
ORDER BY SchemaName, TableName, ColumnName;

-- 舊版相容寫法
SELECT 
  OBJECT_NAME(tab.id) AS TableName,
  col.name AS ColumnName
FROM sysobjects tab
JOIN syscolumns col ON tab.id = col.id
WHERE tab.xtype = 'U' AND col.name LIKE '%m_Id%'
ORDER BY 1,2;

四、進階：動態呼叫預存程序

DECLARE @ProcName NVARCHAR(128) = N'sys.sp_AddProduct';
DECLARE @SQL NVARCHAR(MAX) = N'EXEC ' + QUOTENAME(@ProcName) + N' @ViewCount=10, @HotCount=5 OUTPUT';
DECLARE @HotCount INT;
EXEC sp_executesql @SQL, N'@HotCount INT OUTPUT', @HotCount OUTPUT;
PRINT @HotCount;

五、常見錯誤與排查

結語

掌握 OUTPUT 雙向傳遞、sp_executesql 參數化動態呼叫、系統目錄查詢欄位分佈，是 SQL Server 開發與維護的核心技能。建議將常用預存程序封裝為應用層 Repository 方法，統一參數驗證與異常處理。