T-SQL How get last T-Sql command from parameter with a list of commands-CodePudding

My app sends to sql a few commands in one parameter, I need substring a text with only the last command and execute it. I know, that the last command is starting from 'select', not starting e.g. 'declare' the last statement can be complicated e.g. inner selecting, or simply "select * from ...."

In my app (system reporting) admin users prepared lists of statements (similar to creating procedures in t-sql) and the last statement is always a table with results. My task is to use these statements as a pre-prepare at night results in XML format via SQL agent. I need to add to the last statement "select ( last statement for xml path('Table'))". How to find the last statement.

e.g.


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) ```

CodePudding user response：

A code finds the last SELECT statement. I assumed that any repeated word "SELECT" in the SELECT statement is always between round brackets e.g.

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'.

A code create copy of commands and destroy any 'select' word in inner the SELECT statement via replacing the letter 'S' with space. And then the program to find the position of last statement is searching last 'select' word in copy of commands.

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)

CodePudding user response：

A another code, finds the last 'SELECT' statement in case when list commands is with comments. In t-sql the commands can include not closed brackets I added removing all comments before to investigate the position of the last statement

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)