请问一个关于表定义查询的有关问题，多谢

请教一个关于表定义查询的问题，谢谢。

SQL code

    DECLARE @TempTable TABLE([NodeID] INT);    DECLARE @SqlStrNode NVARCHAR(MAX);    IF(@Level='Lower')        BEGIN            SET @SqlStrNode = N'                SELECT NodeB.[pkID]                FROM [dbo].[MS_Node] NodeA                LEFT JOIN [dbo].[MS_Node] NodeB                ON NodeA.[pkID] = NodeB.[ParentID]                WHERE NodeA.[pkID] IN(' + @NodeID + ')            ';            INSERT INTO @TempTable            EXEC(@SqlStrNode);        END    ELSE IF(@Level='Lowers')        BEGIN            SET @SqlStrNode = N'                SELECT NodeA.[pkID]                FROM [dbo].[MS_Node] NodeA                LEFT JOIN [dbo].[MS_Node] NodeB                ON NodeA.[pkID] = NodeB.[ParentID]                WHERE NodeA.[pkID] IN(' + @NodeID + ')            ';            INSERT INTO @TempTable            EXEC(@SqlStrNode);        END    ELSE        BEGIN            INSERT INTO @TempTable            SELECT @NodeID;        END    DECLARE @SqlStrInfo NVARCHAR(MAX);    SET @SqlStrInfo = N'    SELECT * FROM    (        SELECT InfoA.[pkID] AS [InfoID], InfoA.[NodeID], InfoA.[Title]        ,ROW_NUMBER() OVER        (            ORDER BY InfoA.[pkID] DESC        ) AS RowNum        FROM [dbo].[MS_Info] InfoA        LEFT JOIN [dbo].[MS_Info_Base] InfoBaseA        ON InfoA.pkID = InfoBaseA.InfoID        LEFT JOIN [dbo].[MS_Info_Ext_File] InfoExtA        ON InfoA.pkID = InfoExtA.InfoID        LEFT JOIN [dbo].[MS_Node] NodeA        ON InfoA.pkID = NodeA.pkID        LEFT JOIN [dbo].[MS_Node_Ext] NodeExtA        ON InfoA.pkID = NodeExtA.NodeID        WHERE InfoA.[NodeID] IN(' + @NodeID + ') --这里，我想做成：SELECT [NodeID] FROM @TempTable    ) AS TempTable    WHERE RowNum BETWEEN ' + RTRIM(@BetweenBegin) + ' AND ' + RTRIM(@BetweenEnd) + '    ';    EXEC(@SqlStrInfo)

SELECT [NodeID] FROM @TempTable
这个语句用在这里会出错，应该怎么做？谢谢。
前提是不要动这段SQL的结构。

[解决办法]
WHERE InfoA.[NodeID] IN(' + @NodeID + ')'
-->
WHERE InfoA.[NodeID] IN( SELECT [NodeID] FROM ' + @TempTable + ')'
[解决办法]
你定义的@TempTable是一个临时表,为什么你觉得 select * from @TempTable不能用呢?

SQL code

--测试Code DECLARE @TempTable TABLE([NodeID] INT);  insert into @TempTable select 1 union all select 2  select * from @TempTable
[解决办法]

你这种情况下动态SQL是不能使用表变量的，@temptable的的定义是在字符串之外的，你直接拼接只会报错。如果不想修改上面语句的结构的话，你只能把表变量换成临时表才可以。 

 

查看更多 下一篇