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( SELECT [NodeID] FROM ' + @TempTable + ')'
--测试Code
DECLARE @TempTable TABLE([NodeID] INT);
insert into @TempTable
select 1 union all
select 2
select * from @TempTable
你这种情况下动态SQL是不能使用表变量的,@temptable的的定义是在字符串之外的,你直接拼接只会报错。如果不想修改上面语句的结构的话,你只能把表变量换成临时表才可以。