sql server基本流程语句

 

 

 

1、IF...ELSE (sql server没有end,oracle中有)

DECLARE  @co INT
SET @co=(SELECT COUNT(1) FROM HGOS_UC.dbo.Emp_Ora_Role eor WHERE eor.EmpId=539 AND eor.DepId=35)
IF @co=0
    SELECT cr.*
    FROM HGOS.dbo.CustomerRM AS cr
    JOIN HGOS_WF.dbo.Task AS t ON cr.CompanyCode = t.OID
    LEFT JOIN HGOS.dbo.Customer_Ora_R AS cor ON cor.CId=cr.Id
    WHERE cor.OrgId=35
    AND t.State = 4  AND ISNULL(t.IsDelete,0) = 0 AND ISNULL(cr.IsFreeze,0) = 0
    AND cr.CreateUserId=539
ELSE
    SELECT cr.*
    FROM HGOS.dbo.CustomerRM AS cr
    JOIN HGOS_WF.dbo.Task AS t ON cr.CompanyCode = t.OID
    LEFT JOIN HGOS.dbo.Customer_Ora_R AS cor ON cor.CId=cr.Id
    WHERE cor.OrgId=35
    AND t.State = 4  AND ISNULL(t.IsDelete,0) = 0 AND ISNULL(cr.IsFreeze,0) = 0

2、CASE...WHEN...THEN

SELECT
t.ID,
CASE
    WHEN STATE=1 THEN '审批中'
    WHEN STATE=2 THEN '已通过'
    ELSE '已驳回'
END
FROM HGOS_WF.dbo.Task AS t;