MSSQL 확장 함수 구현

1.  CLR 통합 사용 설정

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO

2. DLL Sample 작성(숫자금액 --> 한글금액 으로 변환)

using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.SqlServer.Server;
using System.Data.SqlTypes;

public class T
{
    [SqlFunction(DataAccess = DataAccessKind.None)]
    static public string Number2Hangle(long lngNumber)
    {
        string strHangul = string.Empty;
        string strSub = string.Empty;
        string[] NumberChar = new string[] { "", "일", "이", "삼", "사", "오", "육", "칠", "팔", "구" };
        string[] LevelChar = new string[] { "", "십", "백", "천" };
        string[] DecimalChar = new string[] { "", "만", "억", "조", "경" };

        if (lngNumber.ToString().Length > 20) return "Error";

        string strNumber = string.Format("{0:0000 0000 0000 0000 0000}", lngNumber);
        string[] arrNumber = strNumber.Split(' ');

        for (int j = arrNumber.Length - 1; j >= 0; j--)
        {
            strSub = "";
            if (int.Parse(arrNumber[j]) != 0)
            {
                for (int i = arrNumber[j].Length - 1; i >= 0; i--)
                {
                    int nNum = int.Parse(arrNumber[j].Substring(i, 1));
                    if (nNum != 0)
                    {
                        strSub = NumberChar[nNum] + LevelChar[arrNumber[j].Length - i - 1] + strSub;
                    }
                }
                strHangul = strSub + DecimalChar[arrNumber.Length - j - 1] + strHangul;
            }
        }
        return strHangul;
    }

}

3. Assembly 등록

CREATE ASSEMBLY Number2Hangle FROM 'C:\MSSQL_Library\Number2Hangle.dll' WITH PERMISSION_SET = SAFE
GO

4. Function 등록

ALTER FUNCTION Number2Hangle(@lngNumber BIGINT) RETURNS NVARCHAR(50)
AS EXTERNAL NAME Number2Hangle.T.Number2Hangle;
GO

5. 테스트

SELECT dbo.Number2Hangle(1000000)
GO