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

Question

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

This looks like an [x-y problem](https://meta.stackexchange.com/questions/66377/what-is-the-xy-problem). Side note, literals in T-SQL should be enclosed in single quotes, not double quotes, and sessions should use `QUOTED IDENTIFIER ON`. Parsing T-SQL using T-SQL is non-trivial. Don't go there. — Dan Guzman, Oct 06 '21 at 11:41
SQL is not a good choice of language to parse itself. In fact, it's text manipulation is, in truth, terrible. If you *have* to do this, you'll need to do it with something other than T-SQL. — Thom A, Oct 06 '21 at 11:46
Depends where the statement(s) are coming from, @DávidLaczkó . The statements above, oddly, don't actually have anything injected into it, so they're not actually able to suffer injection. Of course, how that statement was made is another question/problem entirely, and that *might* be vulnerable to injection. — Thom A, Oct 06 '21 at 11:48
If I not find solution, Ultimately I will have to change app, that it will add some comments e.g. / * next statement * / between statements, and next in T-sql I will use charindex, reverse function to find last command. — Adam Rydzewski, Oct 06 '21 at 11:53
@AdamRydzewski, if you change the app, consider passing the statement list with index as a table-valued parameter or some other structured list (XML or JSON). That will avoid the T-SQL parsing ugliness. — Dan Guzman, Oct 06 '21 at 12:02
If also you're rewriting the statements then you should take the time to properly terminate turn too. — Thom A, Oct 06 '21 at 12:12
I think you're just looking for the last occurrence of a substring. Maybe. I might be wrong. Here's a solution for that: https://stackoverflow.com/questions/1024978/find-index-of-last-occurrence-of-a-sub-string-using-t-sql — Grant Fritchey, Oct 06 '21 at 12:32
Briefly, parsing T-SQL from T-SQL is not doable. If you can't enforce termination this is a lost cause. `SELECT 1 [SELECT] SELECT 2 [FROM] FROM (SELECT "FROM" FROM [SELECT]) "SELECT"` is two valid T-SQL statements, assuming a table named `SELECT` with a column named `FROM` is in the database. Can you write code that handles this flawlessly, and more importantly, would you want to? — Jeroen Mostert, Oct 06 '21 at 12:33
@DanGuzman Thank you. Good idea to use a XML. Today is prepared more than 1000 of those lists. I added some details about my app in the question area. Maybe have you another idea. — Adam Rydzewski, Oct 06 '21 at 12:37
How does the SQL Server split statements from T-SQL procedure before executing their? Does anybody know ? Maybe it is way to find a solution. — Adam Rydzewski, Oct 06 '21 at 12:45
The SQL server engineers know; the T-SQL parser is a hand-crafted piece of code that does things you cannot (easily) replicate in T-SQL. There are managed reimplementations of it ([`TSqlParser`](https://learn.microsoft.com/dotnet/api/microsoft.sqlserver.transactsql.scriptdom.tsqlparser) from DacFx) but I would strongly recommend not relying on this if you don't absolutely have to, and instead ensure the input is always easy to process. — Jeroen Mostert, Oct 06 '21 at 12:51
@AdamRydzewski. T-SQL can be [parsed in .NET app code](https://www.dbdelta.com/microsoft-sql-server-script-dom/) fairly easily but doing so in T-SQL is the challenge. — Dan Guzman, Oct 06 '21 at 13:45

Adam Rydzewski · Answer 1 · 2021-10-07T06:32:52.663

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)

What are the merits of `((select SUBSTRING(@CopyOfCommands,@i,1)) = '(')` over `Substring( @CopyOfCommands, @i, 1 ) = '('`? It isn't clear that adding `select` and parentheses add value. — HABO, Oct 06 '21 at 14:59
@HABO In this case no merit. Habittaly I add 'select' in 'where' clause e.g. select ... from ... where (select getdate()) between dateFrom and DateTo), because this is more efficient for SQL engine. You have right, without "select' is more clearly code. I updated a code. Thanks. — Adam Rydzewski, Oct 07 '21 at 06:17
I'd love to learn about the improved efficiency of using `( select GetDate() )`. Can you cite a source for that information? — HABO, Oct 07 '21 at 13:08

score 0 · Answer 2 · answered Oct 07 '21 at 11:40

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)

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

2 Answers2