我的應用程式在一個引數中向 sql 發送了幾個命令，我需要只包含最后一個命令的文本子串并執行它。我知道，最后一個命令是從“select”開始，而不是從“declare”開始，最后一條陳述句可能很復雜，例如內部選擇，或者只是“select * from ....”
在我的應用程式（系統報告）中，管理員用戶準備了陳述句串列（類似于在 t-sql 中創建程序），最后一個陳述句始終是一個帶有結果的表。我的任務是通過 SQL 代理使用這些陳述句作為 XML 格式的夜間結果的預準備。我需要添加到最后一條陳述句“select（ xml path('Table')) 的最后一條陳述句”。如何找到最后一個陳述句。
例如
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 @lastCommand = ....
execute (@lastCommand) ```
uj5u.com熱心網友回復：
代碼查找最后一個 SELECT 陳述句。我假設 SELECT 陳述句中任何重復的單詞“SELECT”總是在圓括號之間，例如
select *, b=(select top 1 id from table3 where id >10), x.Total from table4 y inner join (select Id, date from table5) x on x.Id = y.Id'。
代碼創建命令副本并通過用空格替換字母“S”來破壞 SELECT 陳述句內部的任何“選擇”字。然后查找最后一條陳述句位置的程式是在命令副本中搜索最后一個“選擇”字。
declare @commands varchar(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), x.Total
from table4 y
inner join (select Id, date from table5) x on x.Id = y.Id
'
declare @CopyOfCommands varchar(max)
set @CopyOfCommands = @commands
declare @lenCommandsText int
declare @i int = 0
declare @brockets int = 0
set @lenCommandsText = LEN(@CopyOfCommands)
while (@i < @lenCommandsText)
--Replacing any 'select' word to ' elect' between brockets
begin
if ((SUBSTRING(@CopyOfCommands,@i,1)) = '(')
set @brockets = @brockets 1
else if ((SUBSTRING(@CopyOfCommands,@i,1)) = ')')
set @brockets = @brockets - 1
else if ((UPPER(SUBSTRING(@CopyOfCommands,@i,1))) = 'S' and @brockets > 0)
set @CopyOfCommands = SUBSTRING(@CopyOfCommands,0,@i) ' ' SUBSTRING(@CopyOfCommands,@i 1,@lenCommandsText - @i)
set @i = @i 1
end
-- Finding a position of the word 'SELECT' in the reverse text of commands (Word 'SELECT' => 'TCELES'). Converting result to reverse position in original commands list
declare @lastCommand varchar(max)
set @lastCommand = (select SUBSTRING(@commands,@lenCommandsText - CHARINDEX('TCELES',Upper(REVERSE(@CopyOfCommands)))-4,CHARINDEX('TCELES',Upper(REVERSE(@CopyOfCommands))) 5))
select @lastCommand
--execute (@lastCommand)
uj5u.com熱心網友回復：
另一個代碼，在串列命令帶有注釋的情況下查找最后一個 'SELECT' 陳述句。在 t-sql 中，命令可以包含未封閉的括號，我添加了洗掉所有注釋之前調查最后一條陳述句的位置
set @commands =
'select * from table1
/* it is some comment with start brackets e.g. (( ok
select A, b=(select top 1 id from table3 where id >10) from table2
-- it is some comment with start brackets e.g. (( ok
select Number, count(*) from table3 group by Number
select *, b=(select top 1 id from table3 where id >10), x.Total
from table4 y
inner join (select Id, date from table5) x on x.Id = y.Id
'
declare @CopyOfCommands varchar(max)
set @CopyOfCommands = @commands
declare @lenCommandsText int
declare @i int = 0
declare @brockets int = 0
declare @commentType int = 0 /*0 - none, 1 - Single Line Comments, 2.. - Multi-line Comments */
set @lenCommandsText = LEN(@CopyOfCommands)
while (@i < @lenCommandsText)
--Replacing any 'select' word to ' elect' between brockets
begin
--if (SUBSTRING(@CopyOfCommands,@i,2) = '/*')
-- select @commentType, SUBSTRING(@CopyOfCommands,@i,2), SUBSTRING(@CopyOfCommands,@i-20,100)
if @commentType = 0 and SUBSTRING(@CopyOfCommands,@i,2) = '--'
begin
set @commentType = 1
set @i = @i 2
continue
end
else if @commentType = 0 and SUBSTRING(@CopyOfCommands,@i,2) = '/*'
begin
set @commentType = 2
set @i = @i 2
continue
end
else if @commentType = 1
begin
if SUBSTRING(@CopyOfCommands,@i,1) = CHAR(13)
set @commentType = 0
else
/*remove a comment text in copy of commands*/
set @CopyOfCommands = SUBSTRING(@CopyOfCommands,0,@i) ' ' SUBSTRING(@CopyOfCommands,@i 1,@lenCommandsText - @i)
end
else if @commentType > 1
begin
if SUBSTRING(@CopyOfCommands,@i,2) = '/*' -- nested comments
begin
set @commentType = @commentType 2
set @i = @i 2
continue
end
else if SUBSTRING(@CopyOfCommands,@i,2) = '*/'
begin
set @commentType = @commentType - 2
set @i = @i 2
continue
end
else
/*remove a comment text in copy of commands*/
set @CopyOfCommands = SUBSTRING(@CopyOfCommands,0,@i) ' ' SUBSTRING(@CopyOfCommands,@i 1,@lenCommandsText - @i)
end
else if ( SUBSTRING(@CopyOfCommands,@i,1)) = '('
set @brockets = @brockets 1
else if (SUBSTRING(@CopyOfCommands,@i,1)) = ')'
set @brockets = @brockets - 1
else if UPPER(SUBSTRING(@CopyOfCommands,@i,1)) = 'S' and @brockets > 0
set @CopyOfCommands = SUBSTRING(@CopyOfCommands,0,@i) ' ' SUBSTRING(@CopyOfCommands,@i 1,@lenCommandsText - @i)
set @i = @i 1
end
-- Finding a position of the word 'SELECT' in the reverse text of commands (Word 'SELECT' => 'TCELES'). Converting result to reverse position in original commands list
declare @lastCommand varchar(max)
set @lastCommand = (select SUBSTRING(@commands,@lenCommandsText - CHARINDEX('TCELES',Upper(REVERSE(@CopyOfCommands)))-4,CHARINDEX('TCELES',Upper(REVERSE(@CopyOfCommands))) 5))
select @lastCommand
--execute (@lastCommand)
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/313291.html
標籤：sql-server 查询语句