CTE之后如何使用if语句（SQL Server 2005） [英] How can I use if statement after a CTE (SQL Server 2005)

问题描述

昨晚我正在编写一个类似以下内容的简单T-SQL程序

Last night I was writing a simple T-SQL program something like this

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

编译后，我发现它抛出了类似如果...附近的语句不正确之类的错误

I found after compilation that it is throwing error something like "Incorrect statement near if"

出什么问题了？

但是，我通过其他方式做到了这一点。但是我想知道为什么它不起作用！

However, I did that by using some other way. But I wanted to know why it did not work!

推荐答案

公用表表达式在单个语句的上下文中定义：

Common table expressions are defined within the context of a single statement:

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

因此，您可以执行以下操作：

So you can do something like:

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

或

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

CTE后面必须是单个
SELECT， INSERT，UPDATE，MERGE或
DELETE语句引用了某些
或所有CTE列。也可以在CREATE VIEW
语句中指定CTE
作为视图的定义
SELECT语句的一部分

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

这篇关于CTE之后如何使用if语句（SQL Server 2005）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！