请问一个关于表定义查询的有关问题,多谢
请教一个关于表定义查询的问题,谢谢。
SELECT [NodeID] FROM @TempTable
这个语句用在这里会出错,应该怎么做?谢谢。
前提是不要动这段SQL的结构。
------解决方案--------------------
WHERE InfoA.[NodeID] IN(' + @NodeID + ')'
-->
WHERE InfoA.[NodeID] IN( SELECT [NodeID] FROM ' + @TempTable + ')'
------解决方案--------------------
你定义的@TempTable是一个临时表,为什么你觉得 select * from @TempTable不能用呢?
- 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的的定义是在字符串之外的,你直接拼接只会报错。如果不想修改上面语句的结构的话,你只能把表变量换成临时表才可以。