如何从包含多条T-SQL命令的参数中提取最后一条select语句执行

核心逻辑 #

我们需要定位的是最末尾、不属于任何子查询/嵌套结构的顶级SELECT关键字：子查询内的select均包裹在括号内部，而顶级SELECT的前方不存在未闭合的左括号，以此作为识别依据。

实现代码 #

对应示例场景，完整实现如下：

declare @commands nvarchar(max)
set @commands = 
'select * from table1 

 select A, b=(select top 1 id from table3 where id >10) from table2

 select Number, count(*) from table3 group by Number

 select *, b=(select top 1 id from table3 where id >10) from table2, x.Total 
  from table4 y
  inner join (select Id, date from table5) x on  x.Id = y.Id
'

-- 反转字符串从后往前查找，避免子查询干扰
declare @reversedCommands nvarchar(max) = reverse(@commands)
declare @lastSelectPos int = 0
declare @bracketCount int = 0
declare @currentPos int = 1
declare @totalLen int = len(@reversedCommands)

while @currentPos <= @totalLen - 5 -- SELECT反转后为TCELES，长度为6，需预留足够字符
begin
    -- 匹配反转后的SELECT关键字，不区分大小写
    if upper(substring(@reversedCommands, @currentPos, 6)) = 'TCELES'
    begin
        -- 括号计数为0说明是顶级SELECT
        if @bracketCount = 0
        begin
            set @lastSelectPos = @totalLen - @currentPos + 1
            break
        end
    end
    -- 维护括号计数：反转后右括号对应原语句左括号，左括号对应原语句右括号
    if substring(@reversedCommands, @currentPos, 1) = ')'
        set @bracketCount = @bracketCount + 1
    else if substring(@reversedCommands, @currentPos, 1) = '('
        set @bracketCount = @bracketCount - 1
    
    set @currentPos = @currentPos + 1
end

-- 提取最后一条SELECT语句
declare @lastSelectCommand nvarchar(max) = ltrim(rtrim(substring(@commands, @lastSelectPos, @totalLen - @lastSelectPos + 1)))
-- 拼接XML生成逻辑
declare @finalCommand nvarchar(max) = 'select ( ' + @lastSelectCommand + ' for xml path(''Table''))'
-- 执行最终语句
execute (@finalCommand)

注意事项 #

• 若语句中包含注释，需要先预处理移除--开头的行注释和/* */包裹的块注释，避免注释内的select关键字干扰识别
• 仅支持括号完全成对闭合的合规T-SQL语句
• 识别逻辑自动适配select关键字的大小写格式

内容的提问来源于stack exchange，提问作者Adam Rydzewski