您现在的位置：程式碼之家 >> 數據庫知識 > >> SQL Server 資料庫 >> 關於SqlServer

SQL Server 存儲過程開發經驗總結：輸出參數、關鍵字轉義、動態 SQL 臨時表、事務處理、ActiveX 調用、游標優化

編輯：關於SqlServer

1. 輸出參數（OUTPUT）技巧

CREATE PROCEDURE GetName
  @uid NVARCHAR(1),
  @username NVARCHAR(10) = '' OUTPUT
AS
  SET @username = 'hongchao';
  RETURN 0;

注意：SQL 2000 中若存儲過程只有一個參數且為 OUTPUT，調用時必須給該參數初始值，否則報錯。

2. 關鍵字轉義：方括號 [] 保平安

同樣語句 WHERE level = 1，SQL 7.0 正常，SQL 2000 報錯（level 成為保留字）。
規範：所有可能與保留字衝突的列名/表名/變量名統一加 []：
SELECT * FROM [user] WHERE [level] = 1;

3. sp_executesql 動態 SQL 與臨時表可見性

動態 SQL 內創建的 局部臨時表（#temp）對外部不可見
需用 全局臨時表（##temp） 傳遞數據
優先考慮 sp_executesql 參數化，避免注入並利用計劃緩存

4. 臨時表與游標：用完即刪，能不用就不用

對象	清理動作	性能影響
局部臨時表	`DROP TABLE #temp`（會話結束自動刪，但建議顯式釋放）	TempDB 爭用、日志開銷
全局臨時表	`DROP TABLE ##temp`（最後會話結束自動刪）	跨會話可見，鎖競爭更大
游標	`CLOSE cur; DEALLOCATE cur;`	佔用內存、鎖、TempDB，高併發易崩潰

原則：集合操作優於游標；臨時表僅作中轉，不用不建。

5. 調用外部 ActiveX DLL (sp_OA 系列)

DECLARE @obj INT, @hr INT, @prop VARCHAR(255), @src VARCHAR(255), @desc VARCHAR(255);
EXEC @hr = sp_OACreate 'SQLDMO.SQLServer', @obj OUT;
IF @hr <> 0 BEGIN EXEC sp_OAGetErrorInfo @obj, @src OUT, @desc OUT; RETURN; END

EXEC @hr = sp_OASetProperty @obj, 'HostName', 'Gizmo';
EXEC @hr = sp_OAGetProperty @obj, 'HostName', @prop OUT;
PRINT @prop;

EXEC @hr = sp_OAMethod @obj, 'Connect', NULL, 'my_server', 'my_login', 'my_pass';
EXEC @hr = sp_OADestroy @obj;

場景：需調用 COM 組件（如 SQLDMO、文件操作、加密組件）。
風險：安全性差、穩定性差、64 位環境兼容性問題，**能用 T-SQL/CLR 解決絕不碰 sp_OA**。

6. 事務處理：不可在事務中用 RETURN 強退

BEGIN TRAN;
  UPDATE A SET ...;
  IF @@ERROR <> 0 BEGIN ROLLBACK; RETURN; END  -- 錯誤！RETURN 導致事務未正確提交/回滾
  UPDATE B SET ...;
COMMIT;

正確模式：

BEGIN TRY
  BEGIN TRAN;
    UPDATE A SET ...;
    UPDATE B SET ...;
  COMMIT;
END TRY
BEGIN CATCH
  IF @@TRANCOUNT > 0 ROLLBACK;
  -- 記錄錯誤、拋出或返回錯誤碼
  RETURN -1;
END CATCH;

拆分建議：高頻獨立操作拆分為多個存儲過程，避免長事務阻塞，前提是不破壞業務一致性。