scriptdom add statement to the tree.

If the select statement is within a try/catch or if/else this code will not find the select, it will be written out as a single statement in the else part of the code.

It's difficult to see the error in your ways without the complete code but you will find using a visitor instead of navigating the DOM directly is much easier and robust. This will find statements of interest regardless of where they appear in the code, including nested begin/end constructs. See the Fragment Visitors section of this article for an introduction to script DOM visitors.

Here's a complete console app example that uses a visitor.

using System;
using System.IO;
using System.Collections.Generic;
using Microsoft.SqlServer.TransactSql.ScriptDom;

internal class Program
{
    static void Main(string[] args)
    {
        var SQLProcedureCode = @"
CREATE PROC dbo.Example
AS
BEGIN
    BEGIN TRY

        SELECT 1;

        /*
        some comment
        */
        --another comment

        SELECT 2;

    END TRY
    BEGIN CATCH

        SELECT 3;

    END CATCH;
END;
";

        var visitor = new InstrumentProcVisitor();
        var instrumentedProc = visitor.getInstrumentedProc(SQLProcedureCode);
        Console.WriteLine(instrumentedProc);

    }

    class InstrumentProcVisitor : TSqlConcreteFragmentVisitor
    {

        StringWriter instrumentedProcCode = new StringWriter();
        List<SelectStatement> selectStatements = new List<SelectStatement>();

        public override void Visit(SelectStatement node)
        {
            selectStatements.Add(node);
        }

        public string getInstrumentedProc(string procCreateScript)
        {

            var TheTextReader = new StringReader(procCreateScript);
            var SQLParser = new TSql150Parser(true);
            IList<ParseError> ParseErrors = new List<ParseError>();

            var procFragments = SQLParser.Parse(TheTextReader, out ParseErrors);
            if (ParseErrors.Count > 0)
            {
                throw new InvalidDataException($"{ParseErrors.Count} parse errors in script");
            }

            //this will visit all SELECT statements in the proc and add to selectStatements list
            procFragments.Accept(this);

            var scriptTokenIndex = 0;
            foreach (var selectStatement in selectStatements)
            {
                //copy stuff before this select statement
                for (scriptTokenIndex = scriptTokenIndex; scriptTokenIndex < selectStatement.FirstTokenIndex; ++scriptTokenIndex)
                {
                    instrumentedProcCode.Write(procFragments.ScriptTokenStream[scriptTokenIndex].Text);
                }

                //add instrumentation code before select
                instrumentedProcCode.WriteLine("\r\nPRINT 'Executing SELECT statement';");

                //copy select statement
                for (int tokenIndex = selectStatement.FirstTokenIndex; tokenIndex <= selectStatement.LastTokenIndex; ++tokenIndex)
                {
                    instrumentedProcCode.Write(selectStatement.ScriptTokenStream[tokenIndex].Text);
                }

                //add instrumentation code after select
                instrumentedProcCode.WriteLine("\r\nPRINT 'SELECT statement completed';");

                //set token index to next token after select
                scriptTokenIndex = selectStatement.LastTokenIndex + 1;
            }

            //copy stuff after last select statement
            for (scriptTokenIndex = scriptTokenIndex; scriptTokenIndex < procFragments.ScriptTokenStream.Count; ++scriptTokenIndex)
            {
                instrumentedProcCode.Write(procFragments.ScriptTokenStream[scriptTokenIndex].Text);
            }

            return instrumentedProcCode.ToString();

        }

    }

}

Below is the resultant instrumented code:

CREATE PROC dbo.Example
AS
BEGIN
    BEGIN TRY


PRINT 'Executing SELECT statement';
SELECT 1;
PRINT 'SELECT statement completed';


        /*
        some comment
        */
        --another comment


PRINT 'Executing SELECT statement';
SELECT 2;
PRINT 'SELECT statement completed';


    END TRY
    BEGIN CATCH


PRINT 'Executing SELECT statement';
SELECT 3;
PRINT 'SELECT statement completed';


    END CATCH;
END;

I don't see why not but there are some things that you should keep in mind first.

• Scriptdom is for dynamically building SQL stuff so if you can do it in T-SQL then you should be able to do it via the DOM.
• Calling a sproc is just an EXEC ... call in most cases (ignoring dynamic SQL). There is no way to see inside the sproc from the DOM to instrument anything.
• Using SMO or equivalent you could get the sprocs defined in a database and then instrument the code directly but you'd first have to run it through the TSqlParser to convert it to a semblance of something the DOM could understand. You'd then have to inject new objects into this list in the appropriate spots to produce the updated logic.
• To replace the sproc you'd have to generate an alter procedure call in lieu of a create procedure call.

Providing any existing code you have could be useful in coming up with a better solution.

Can I do this?

Sure. How does your current code using ScriptDOM look like?

The code I have is quite simple.

TheTextReader = new StringReader(SQLProcedureCode);
SQLParser = new TSql150Parser(true);

SQLStatements = SQLParser.ParseStatementList(TheTextReader, out ParseErrors);
if (ParseErrors.Count > 0)
{   foreach (ParseError item in ParseErrors)
{   MTGStatic.LogStringMessage("Message [" + item.Message + "]");
}
}
else
{   MTGStatic.LogStringMessage("Statements Count " + SQLStatements.Statements.Count.ToString());
    foreach (var SQLStatement in SQLStatements.Statements)
    {   ProcessSQLStatement(SQLStatement);
    }
}

...............
if (SQLStatement is SelectStatement)
{   ProcessSelectStatement((SelectStatement)SQLStatement);
}

I can identify the statements I want to insert code before and after but I don't know what the insert statement would look like.

Thanks for providing help with this.

When I find a statement I want to add code around (before and after) how would I insert that code.

You can extract the text of the existing statement from the ScriptTokenStream and prepend/append instrumentation code as desired. Below is sample snippet of this technique that adds code before and after SELECT statements and leaves others unchanged. Note that whitespace and comments before/after statements will not be preserved unless you process the other fragments/tokens as well.

static StringWriter instrumentedCode = new StringWriter();

void ProcessSQLStatement(TSqlStatement SQLStatement)
{

    if (SQLStatement is SelectStatement)
    {
        //code before statement
        instrumentedCode.WriteLine("PRINT 'Executing SELECT statement';");
        //statement code
        instrumentedCode.WriteLine(getFragmentText(SQLStatement));
        //code after statement
        instrumentedCode.WriteLine("PRINT 'SELECT statement completed';");
    }
    else
    {
        //statement without instrumentation
        instrumentedCode.WriteLine(getFragmentText(SQLStatement));
    }

}

static string getFragmentText(TSqlFragment fragment)
{
    var fragmentText = new StringWriter();
    for (int i = fragment.FirstTokenIndex; i <= fragment.LastTokenIndex; ++i)
    {
        fragmentText.Write(fragment.ScriptTokenStream[i].Text);
    }
    return fragmentText.ToString();
}