如何在CTE(SQL Server 2005)之后使用if语句

发布时间：2020-12-12 16:17:31 所属栏目：MsSql教程来源：网络整理

导读：昨天晚上,我正在写一个这样的简单的T-SQL程序 DECLARE @ROLEID AS INTSELECT @ROLEID = [ROLE ID] FROM TBLROLE;WITH CTEAS( SELECT * FROM SOMETABLE)IF (@ROLEID = 1) BEGIN //SOMECODEENDELSE IF(@ROLEID = 2) BEGIN //SOMECODEENDELSEBEGIN //SOMECODEEND

昨天晚上,我正在写一个这样的简单的T-SQL程序

DECLARE @ROLEID AS INT

SELECT @ROLEID = [ROLE ID] FROM TBLROLE

;WITH CTE
AS
( 
    SELECT * FROM SOMETABLE
)
IF (@ROLEID  = 1) 
BEGIN
      //SOMECODE
END
ELSE IF(@ROLEID  = 2) 
BEGIN
      //SOMECODE
END
ELSE
BEGIN 
      //SOMECODE
END

我发现编译之后,它会抛出错误,如“错误的语句,如果”

哪里不对？

但是,我用另外一种方式做到了.但是我想知道为什么它不起作用！

解决方法

Common table expressions在单个语句的上下文中定义：

WITH cte_name AS (
  <cte definition>)
<statement that uses cte>;

所以你可以做一些事情：

WITH CTE
AS
( 
    SELECT * FROM SOMETABLE
)
SELECT * FROM CTE;

要么

WITH CTE
AS
( 
    SELECT * FROM SOMETABLE
)
UPDATE CTE 
SET somefield = somevalue
WHERE id = somekey;

A CTE must be followed by a single SELECT,INSERT,UPDATE,MERGE,or DELETE statement that references some or all the CTE columns. A CTE can also be specified in a CREATE VIEW statement as part of the defining SELECT statement of the view

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!