使用SQL Server 2005“WITH”语句的多个选择语句

I am trying to use the "WITH" statement in SQL Server 2005. It seems to work fine if I do something like:

我试图在SQL Server 2005中使用“WITH”语句。如果我执行以下操作似乎工作正常:

WITH MyBigProducts AS (SELECT * FROM Products WHERE Size='Big')
SELECT Name FROM MyBigProducts

But it fails if I try to use multiple select statements like:

但如果我尝试使用多个select语句,它会失败,例如:

WITH MyBigProducts AS (SELECT * FROM Products WHERE Size='Big')
SELECT Name FROM MyBigProducts
SELECT Count(*) FROM MyBigProducts

and the error message is "Invalid object name 'MyBigProducts'".

并且错误消息是“无效的对象名称'MyBigProducts'”。

Is there something I can do to increase the scope of the "MyBigProducts" table to include both of the select statements?

我可以做些什么来增加“MyBigProducts”表的范围以包含两个select语句吗?

My example is a simplification of my actual code causing the problem, so I'm not discounting the possibility that the above trivial example should work and that there is another bug in my SQL.

我的例子是我的实际代码的简化导致问题,所以我不打算上述简单示例应该工作的可能性以及我的SQL中还有另一个错误。

I have tried wrapping a BEGIN and END around the two SELECT statements, but the parser could not compile it.

我试过在两个SELECT语句周围包装一个BEGIN和END,但解析器无法编译它。

5 个解决方案

#1

As Kane said, the CTE is only available in the SQL statement where it is written. Another possible solution, depending on the specifics of your situation, would be to include the COUNT(*) in the single query:

正如Kane所说,CTE只能在编写它的SQL语句中使用。根据您的具体情况,另一种可能的解决方案是在单个查询中包含COUNT(*):

;WITH MyBigProducts AS
(
     SELECT
          Name,
          COUNT(*) OVER () AS total_count
     FROM
          Products
     WHERE
          Size = 'Big'
)
SELECT
     Name,
     total_count
FROM
     MyBigProducts

#2

I believe that Common Table Expressions are only valid for immediate use which is why you are getting an error for the "SELECT Count(*) FROM MyBigProducts". In order to reuse a CTE you should use a temporary table instead

我相信公用表表达式仅对立即使用有效,这就是“SELECT Count(*)FROM MyBigProducts”出错的原因。为了重用CTE,您应该使用临时表

DECALRE @BigProducts TABLE (...[declaration omitted]...)

INSERT INTO @BigProducts
SELECT * 
FROM Products 
WHERE Size='Big'


SELECT Name FROM @BigProducts
SELECT Count(*) FROM @BigProducts

Please correct me if I am wrong.

如果我错了,请纠正我。

#3

CTE have statement local scope and visibility. If you want greater scope and visibility for a table expression, you would need to make the CTE into a View or table-valued function.

CTE具有声明本地范围和可见性。如果想要更大的表范围和可见性,则需要将CTE设置为View或表值函数。

Alternatively, you can materialize the expressions results into a table variable, which is local to the current batch or a temporary table whose lifetime is also local to the current batch, but whose visibility extends to the whole session.

或者,您可以将表达式结果具体化为表变量,该表变量是当前批处理的本地变量或临时表,其生存期也是当前批处理的本地,但其可见性扩展到整个会话。

#4

CREATE TABLE tblEmployees
(
    EmployeeID    SMALLINT IDENTITY(1001,1) NOT NULL,
    EmployeeName  NVARCHAR(100) NOT NULL,
    Department    NVARCHAR(50) NOT NULL,
    Designation   NVARCHAR(50) NOT NULL,
    JoiningDate   DATETIME NOT NULL,
    Salary        DECIMAL(10,2) NOT NULL,
    [Description] NVARCHAR(1000) NULL 
)

SELECT * FROM tblEmployees

INSERT INTO tblEmployees(EmployeeName, Department, Designation, JoiningDate, Salary, [Description]) 
VALUES  ('John Smith', 'IT Research', 'Research Analyst', '02/08/2005', 23000.00, 'John Smith is involved in the Research and Development since 2005')

INSERT INTO tblEmployees(EmployeeName, Department, Designation, JoiningDate, Salary, [Description]) 
VALUES  ('John Micheal', 'IT Operations', 'Manager', '07/15/2007', 15000.00, NULL)

INSERT INTO tblEmployees(EmployeeName, Department, Designation, JoiningDate, Salary, [Description]) 
VALUES  ('Will Smith', 'IT Support', 'Manager', '05/20/2006', 13000.00, 'Joined this year as IT Support Manager')

INSERT INTO tblEmployees(EmployeeName, Department, Designation, JoiningDate, Salary, [Description]) 
VALUES  ('Anna John', 'IT Support', 'Developer', '02/10/2008', 23000.00, 'Joined this year as IT Support Developer')


DECLARE @EmpID AS SMALLINT
DECLARE @SQLQuery AS NVARCHAR(500)
SET @EmpID = 1001
SET @SQLQuery = 'SELECT * FROM tblEmployees WHERE EmployeeID = ' + CAST(@EmpID AS NVARCHAR(10))
Print @SQLQuery 
EXECUTE(@SQLQuery)


DECLARE @EmpID AS SMALLINT
DECLARE @SQLQuery AS NVARCHAR(500)
DECLARE @ParameterDefinition AS NVARCHAR(100)
SET @EmpID = 1002
SET @SQLQuery = 'SELECT * FROM tblEmployees WHERE EmployeeID = @EmpID' 
SET @ParameterDefinition =  '@EmpID SMALLINT'
EXECUTE sp_executesql @SQLQuery, @ParameterDefinition, @EmpID



Create Procedure sp_EmployeeSelect
    @EmployeeName NVarchar(100),
    @Department NVarchar(50),
    @Designation NVarchar(50),
    @StartDate DateTime,
    @EndDate DateTime,
    @Salary Decimal(10,2)       
AS
    Set NoCount ON  
    Declare @SQLQuery AS NVarchar(4000)
    Declare @ParamDefinition AS NVarchar(2000) 
    Set @SQLQuery = 'Select * From tblEmployees where (1=1) ' 

    If @EmployeeName Is Not Null 
         Set @SQLQuery = @SQLQuery + ' And (EmployeeName = @EmployeeName)'
    If @Department Is Not Null
         Set @SQLQuery = @SQLQuery + ' And (Department = @Department)' 
    If @Designation Is Not Null
         Set @SQLQuery = @SQLQuery + ' And (Designation = @Designation)'
    If (@StartDate Is Not Null) AND (@EndDate Is Not Null)
         Set @SQLQuery = @SQLQuery + ' And (JoiningDate BETWEEN @StartDate AND @EndDate)'
    If @Salary Is Not Null
         Set @SQLQuery = @SQLQuery + ' And (Salary >= @Salary)'
    Set @ParamDefinition = '@EmployeeName NVarchar(100),
                @Department NVarchar(50),
                @Designation NVarchar(50),
                @StartDate DateTime,
                @EndDate DateTime,
                @Salary Decimal(10,2)'

    Exec sp_Executesql  @SQLQuery, @ParamDefinition, @EmployeeName, @Department, @Designation, 
                        @StartDate, @EndDate, @Salary

    If @@ERROR <> 0 GoTo ErrorHandler
    Set NoCount OFF
    Return(0)

ErrorHandler:
    Return(@@ERROR)
GO


EXEC sp_EmployeeSelect 'John Smith', NULL, NULL, NULL, NULL, NULL

EXEC sp_EmployeeSelect NULL, 'IT Operations', 'Manager', NULL, NULL, NULL




DECLARE @EmpName AS NVARCHAR(50)
DECLARE @SQLQuery AS NVARCHAR(500)
SET @EmpName = 'John' 
SET @SQLQuery = 'SELECT * FROM tblEmployees WHERE EmployeeName LIKE '''+ '%' + @EmpName + '%' + '''' 
PRINT @SQLQuery
EXECUTE sp_executesql @SQLQuery



DECLARE @EmpID AS NVARCHAR(50)
DECLARE @SQLQuery AS NVARCHAR(500)
SET @EmpID = '1001,1003,1004,1002' 
SET @SQLQuery = 'SELECT * FROM tblEmployees WHERE EmployeeID IN(' + @EmpID + ')'
EXECUTE sp_executesql @SQLQuery


DECLARE @OrderBy AS NVARCHAR(50)
DECLARE @SQLQuery AS NVARCHAR(500)
SET @OrderBy = 'Department' 
SET @SQLQuery = 'SELECT * FROM tblEmployees Order By ' + @OrderBy
EXECUTE sp_executesql @SQLQuery


WITH RESULTS AS(
SELECT * FROM [dbo].[User]
)`enter code here`

#5

WITH n(id, name) AS 
   (SELECT id, name 
    FROM ABC
    WHERE parent = 44
        UNION ALL
    SELECT m.id, m.name 
    FROM ABC as m, n
    WHERE n.id = m.parent)
SELECT * FROM n