C# 스토어드 프로시저의 매개변수 이름 추출하기

설계자가 제공하는 SQL문을 활용하여 모델을 자동으로 생성하는 과정은 시간을 절약하고 오류를 최소화하는 데 큰 도움이 됩니다. 이러한 자동화 과정에서, 스토어드 프로시저의 매개변수 정보는 필수적인 요소입니다. 이 글에서는 C#을 사용하여 스토어드 프로시저의 매개변수 이름을 효율적으로 추출하는 방법에 대해 알아보겠습니다.

 

ScriptDom 라이브러리 설치

Microsoft.SqlServer.TransactSql.ScriptDom은 T-SQL 스크립트의 구문 분석, 생성 및 수정을 위한 라이브러리입니다. 이 패키지를 통해 T-SQL 코드를 분석하고, 프로그래밍 방식으로 SQL 쿼리를 생성하거나 수정할 수 있습니다.  Microsoft.SqlServer.DacFx는 보다 복잡한 데이터베이스 관리 작업에 필요한 다양한 기능을 제공합니다. 하지만 이 글에서는 간단한 스크립트 분석 기능만 필요하므로, ScriptDom을 활용하겠습니다.

 

패키지 설치는 NuGet에 검색하여 간단하게 설치할 수 있습니다. .NET CLI를 활용할 경우 아래와 같이 입력합니다.

dotnet add package Microsoft.SqlServer.TransactSql.ScriptDom --version 161.8910.0

 

 

스토어드 프로시저 작성

다음은 이 글에서 사용될 예시 스토어드 프로시저입니다. 목표는 @Param1, @Param2, @Param3을 콘솔에 출력하는 것입니다.

DECLARE @MyOutputParameter INT
EXECUTE MyProcedure @Param1 = 1, @Param2 = 'value2', @Param3 = 'value3', @Param4 = @MyOutputParameter OUTPUT
SELECT @MyOutputParameter

 

 

스크립트 파싱

다음으로 T-SQL 쿼리를 파싱하고, 파싱 중 발생한 오류를 확인하는 코드를 다음과 같이 작성합니다.

IList<ParseError> parseErrors;
TSql100Parser sqlParser = new TSql100Parser(true);

// SQL 쿼리 파싱
TSqlFragment sqlFragment = sqlParser.Parse(new StringReader(sqlQuery), out parseErrors);

// 파싱 오류 확인
if (parseErrors.Count > 0)
{
    ParseError firstError = parseErrors[0];
    throw new Exception(firstError.Message);
}

 

• TSql100Parser 객체를 생성합니다. 이 객체는 T-SQL 쿼리를 파싱하는데 사용됩니다. 생성자에 전달된 true는 SQL Server 2008(즉, T-SQL 100) 문법을 사용하겠다는 것을 의미합니다.

Note
Microsoft는 SQL Server의 새로운 버전을 출시할 때 각각에 대한 새로운 T-SQL 파서를 제공하고 있습니다. SQL Server 2019에 해당하는 파서는 TSql150Parser입니다. 실제 프로덕션 환경에서는 사용하는 SQL Server의 버전에 따라 적절한 T-SQL 파서를 선택하면 됩니다. 이 글에서는 간단한 예제를 위한 코드로 TSql100Parser를 사용했습니다.

• TSql100Parser의 Parse 메서드를 사용하여 T-SQL 쿼리를 파싱합니다. 파싱 결과는 TSqlFragment 객체에 저장되며, 파싱 중 발생한 오류는 parseErrors 리스트에 저장됩니다.
• parseErrors 리스트의 Count가 0보다 크다면 파싱 중 오류가 발생한 것입니다. 이 경우, 첫 번째 오류를 가져와서 예외를 발생시킵니다. 이렇게 하면 파싱 오류가 발생했을 때 적절한 처리를 할 수 있습니다.

 

T-SQL 쿼리가 파싱된 결과는 TSqlFragment의 구체적인 하위 클래스들로 구성된 트리 구조입니다.  TSqlFragment는 파싱된 T-SQL 쿼리의 하나의 구성 요소(즉, fragment)를 나타내는 클래스입니다. TSqlFragment는 T-SQL 쿼리의 최상위 클래스이며, 다른 모든 T-SQL 구문 요소 클래스는 이 클래스를 상속받습니다.

이러한 트리 구조로 파싱하는 이유는 T-SQL 쿼리의 구조를 쉽게 이해하고 분석, 변경, 재사용할 수 있고, 비지터 패턴을 사용하여 T-SQL 쿼리의 구조를 효과적으로 순회하고 처리할 수 있기 때문입니다. 비지터 패턴은 객체 구조를 순회하면서 각 객체에 대해 특정 작업을 수행하는 디자인 패턴입니다. T-SQL 쿼리의 구조를 트리로 파싱하면, 비지터 패턴을 사용하여 쿼리의 각 부분에 대해 원하는 작업을 수행할 수 있습니다.

 

 

파라미터 비지터 클래스 생성

TSqlFragmentVisitor를 상속받아 T-SQL 쿼리의 파라미터를 추출하는 역할을 하는 비지터 클래스를 아래와 같이 생성합니다.

// 파라미터를 추출하기 위한 비지터 클래스
class ParameterVisitor : TSqlFragmentVisitor
{
    List<string> _procedureParameters = new List<string>();
    public List<string> ProcedureParameters => _procedureParameters.Distinct().ToList();

    // SQL Statement를 방문하는 메서드
    public override void Visit(TSqlStatement node)
    {
        ExtractProcedureParameters(node);
    }

    // 파라미터를 추출하는 메서드
    void ExtractProcedureParameters(TSqlStatement node)
    {
        if (node is BeginEndBlockStatement blockStatement)
        {
            // BEGIN...END 블록 내의 모든 문장을 순회
            foreach (TSqlStatement childStatement in blockStatement.StatementList.Statements)
            {
                ExtractProcedureParameters(childStatement);
            }
        }
        else if (node is ExecuteStatement executeStatement)
        {
            // EXECUTE 문장의 모든 매개변수를 순회
            foreach (ExecuteParameter parameter in executeStatement.ExecuteSpecification.ExecutableEntity.Parameters)
            {
                // 출력 매개변수는 건너뜀
                if (!parameter.IsOutput)
                {
                    _procedureParameters.Add(parameter.Variable.Name);
                }
            }
        }
    }
}

• TSqlFragmentVisitor의 Visit 메서드를 오버라이드하여 TSqlStatement 객체를 방문할 때 파라미터를 추출하는 작업을 수행하도록 합니다.
• ExtractProcedureParameters는 TSqlStatement 객체에서 파라미터를 추출하는 메서드입니다. 이 메서드는 BEGIN...END 블록 내의 모든 문장을 순회하거나, EXECUTE 문장의 모든 매개변수를 순회하면서 파라미터를 추출합니다.
• 출력 파라미터는 건너뜁니다.

 

 

스토어드 프로시저 파라미터 추출

파싱된 SQL 쿼리에서 파라미터 비지터 클래스를 통해 파라미터를 추출하고, 그 결과를 콘솔에 출력합니다.

// 파라미터 추출을 위한 비지터 생성
ParameterVisitor paramVisitor = new();
// 파싱된 SQL 트리를 방문하여 파라미터 추출
sqlFragment.Accept(paramVisitor);
return paramVisitor.ProcedureParameters;

...

// SQL 쿼리에서 저장 프로시저의 매개변수를 추출
List<string> extractedParams = ExtractProcedureParameters(sqlQuery);

// 추출된 매개변수를 출력
foreach (string param in extractedParams)
{
    Console.WriteLine(param);
}

 

실행하면 콘솔에 파라미터 이름이 출력되는 것을 확인할 수 있습니다.

 

 

전체 코드

using Microsoft.SqlServer.TransactSql.ScriptDom;

class Program
{
    static void Main(string[] args)
    {
        // SQL 쿼리 예시
        string sqlQuery = """
            DECLARE @MyOutputParameter INT
            EXECUTE MyProcedure @Param1 = 1, @Param2 = 'value2', @Param3 = 'value3', @Param4 = @MyOutputParameter OUTPUT
            SELECT @MyOutputParameter
            """;

        // SQL 쿼리에서 저장 프로시저의 매개변수를 추출
        List<string> extractedParams = ExtractProcedureParameters(sqlQuery);

        // 추출된 매개변수를 출력
        foreach (string param in extractedParams)
        {
            Console.WriteLine(param);
        }
    }

    // SQL 쿼리에서 저장 프로시저의 매개변수를 추출하는 메서드
    public static List<string> ExtractProcedureParameters(string sqlQuery)
    {
        IList<ParseError> parseErrors;
        TSql100Parser sqlParser = new TSql100Parser(true);

        // SQL 쿼리 파싱
        TSqlFragment sqlFragment = sqlParser.Parse(new StringReader(sqlQuery), out parseErrors);

        // 파싱 오류 확인
        if (parseErrors.Count > 0)
        {
            ParseError firstError = parseErrors[0];
            throw new Exception(firstError.Message);
        }

        // 파라미터 추출을 위한 비지터 생성
        ParameterVisitor paramVisitor = new();
        // 파싱된 SQL 트리를 방문하여 파라미터 추출
        sqlFragment.Accept(paramVisitor);
        return paramVisitor.ProcedureParameters;
    }
}

// 파라미터를 추출하기 위한 비지터 클래스
class ParameterVisitor : TSqlFragmentVisitor
{
    List<string> _procedureParameters = new List<string>();
    public List<string> ProcedureParameters => _procedureParameters.Distinct().ToList();

    // SQL Statement를 방문하는 메서드
    public override void Visit(TSqlStatement node)
    {
        ExtractProcedureParameters(node);
    }

    // 파라미터를 추출하는 메서드
    void ExtractProcedureParameters(TSqlStatement node)
    {
        if (node is BeginEndBlockStatement blockStatement)
        {
            // BEGIN...END 블록 내의 모든 문장을 순회
            foreach (TSqlStatement childStatement in blockStatement.StatementList.Statements)
            {
                ExtractProcedureParameters(childStatement);
            }
        }
        else if (node is ExecuteStatement executeStatement)
        {
            // EXECUTE 문장의 모든 매개변수를 순회
            foreach (ExecuteParameter parameter in executeStatement.ExecuteSpecification.ExecutableEntity.Parameters)
            {
                // 출력 매개변수는 건너뜀
                if (!parameter.IsOutput)
                {
                    _procedureParameters.Add(parameter.Variable.Name);
                }
            }
        }
    }
}

 

 

이 글에서는 T-SQL 쿼리를 파싱하여 스토어드 프로시저의 파라미터 이름을 추출하는 방법에 대해 알아보았습니다. T-SQL 쿼리를 파싱하고 분석하면 위에서 살펴본 파라미터 이름 추출 이외에도 파라미터 값 추출, 특정 테이블이나 컬럼 참조 찾기, 쿼리 수정 및 재작성 등 다양한 작업들을 수행할 수 있습니다.