SQL 实践优化 SUM NULL EXISTS TRANSACTION

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.