SQL 实践优化
2015年8月21日
8:43
（一）SUM 忽略NULL，返回NULL
@AA = SUM(ISNULL(A,0)) 在取不到值得时候就会返回NULL
@BB = ISNULL(SUM(B),0) 这样就算取不到值也会有返回值不会为NULL
（二）NULL字符串拼接
--校验客户是否存在，返回所有不存在的客户编号：
SET @sConsigneeID = NULL
SELECT @sConsigneeID = ISNULL(@sConsigneeID + ',', '') + ConsigneeID
FROM (
SELECT DISTINCT a.ConsigneeID AS ConsigneeID
FROM @tblAllData a
LEFT JOIN BAS_Customer co ON a.ConsigneeID = co.CustomerID AND co.Customer_Type = 'CO'
WHERE
co.CustomerID IS NULL ) a
IF @sConsigneeID <> ''
SET @Msg = @Msg + '客户档案不存在：' + @sConsigneeID
（三）EXISTS
一般都使用EXIST去代替Count这样会有更高的执行效率
（四）TRANSACTION
SET @iTranCount = @@TRANCOUNT
BEGIN TRY
IF @iTranCount = 0
BEGIN TRANSACTION
IF @iTranCount = 0
BEGIN
IF XACT_STATE() = 1
COMMIT TRANSACTION
ELSE
RAISERROR ('事务提交失败！', 16, 0)
END
RETURN 0
END TRY
BEGIN CATCH
IF @iTranCount = 0 AND XACT_STATE() <> 0
ROLLBACK TRANSACTION
SET @Msg = ERROR_MESSAGE()
RETURN -1
END CATCH
4.1 XACT_STATE()
IF (XACT_STATE() = 1) AND (@itc = 0) COMMIT TRAN;
1 -- The session has an active transaction. The session can perform any actions, including writing data and committing the transaction.
0 -- There is no transaction active for the session.
-1 -- The session has an active transaction, but an error has occurred that has caused the transaction to be classified as an uncommittable transaction.