Finally, Enumerations in SQL Server!

RGushue

Hello All,

I create a little trick to provide myself some Enum functionality in SQL Server and I figured I'd pass it along.

SQL Server does not have developer Enumerations (ie. A set of unique values that can be referenced via Intellisense to change a string into an integer value, or codeset key value). 

So, I thought about writing a plugin for SSMS to modify the interface to allow me to reference my project's enumerations. However, I realized that I could probably leverage Redgate SQL Prompt.

By creating snippets with names like
enu_CodesetType_KeyValue1_Description1
I could create enumerations that I could use for looking up an enumerations name, or even an enumerations value due to the great fuzzy search provided by the IntelliSense in SQL Prompt.

Example Snippets for a fictional color codeset:

enu_colorType_01_Blue

enu_colorType_02_Red

enu_colorType_03_Yellow

By typing "enucb" SQL Prompt would provide me the first of my enumerations, the blue.
By typing "enu_color3" SQL Prompt would provide me the enumeration for yellow.

When I choose the enumeration offered by SQL Prompt I decided to output the following values as part of the snippet code:
keyvalue -- Description
Example:
1 -- Blue

Instead of using a @variable for a codeset value, my current project puts a hard coded value with a comment. This allows us to step through code without having to run the entire procedure just to ensure our parameters have a value at runtime.

I understand that this is not a perfect solution to the lack of Enums. If the codeset changes, every place where the value was hardcoded must be changed. We accepted that compromise to make our code easier to debug.

At a minimum these snippets do provide an easy way to look up enumeration by the key or the by description.

Below is some code to automate the process of creating the snippet files for SQL Prompt:

--Description: Demonstrate code to create Snippet files that can serve as a makeshift Enumeration in SSMS
--             By leveraging Snippets in Redgate Prompt

--------------------------------------------------------------------
-- If you haven't already, turn on Ole Automation
--------------------------------------------------------------------

    --Turn options on in order to output files
    EXEC sp_configure 'show advanced options', 1
    GO
    EXEC sp_configure 'Ole Automation Procedures', 1;
    GO
    RECONFIGURE;
    GO

    ----Turn options off -- to restore security
    --EXEC sp_configure 'show advanced options', 1
    --GO
    --EXEC sp_configure 'Ole Automation Procedures', 0;
    --GO
    --RECONFIGURE;
    --GO

GO

--------------------------------------------------------------------
-- Create procedure to output text file
--
--------------------------------------------------------------------

    --Original code taken from  here (modified slightly):
    --https://stackoverflow.com/questions/38599667/how-to-select-each-row-in-the-result-to-a-different-text-file-in-sql-server
    CREATE OR ALTER PROCEDURE #prc_Write_To_File (@text as Varchar(Max), @Filename Varchar(200)) AS
    Begin
        Declare @Object int,@rc int,@FileID Int

        EXEC @rc = sp_OACreate 'Scripting.FileSystemObject', @Object OUT
        EXEC @rc = sp_OAMethod @Object , 'OpenTextFile' , @FileID OUT , @Filename , 2 , 1
        Set @text = Replace(Replace(Replace(@text,'&','&'),'<' ,'<'),'>','>')
        EXEC @rc = sp_OAMethod @FileID , 'WriteLine' , Null , @text
        Exec @rc = sp_OADestroy @FileID
        Declare @Append bit
        Select @Append = 0
        If @rc <> 0
        Begin
            Exec @rc = sp_OAMethod @Object, 'SaveFile',null,@text ,@Filename,@Append
        End
        Exec @rc = sp_OADestroy @Object
    END
    
    GO

----------------------------------
-- Sample table to show the code working
----------------------------------

    DROP TABLE IF EXISTS #tblCodesColor
    CREATE TABLE #tblCodesColor(ColorTypeId INTEGER, ColorTypeEnglishDescription  varchar(50))

    INSERT INTO #tblCodesColor
        (
            ColorTypeId, ColorTypeEnglishDescription
        )
    VALUES
        ( 1, 'Blue'),
        ( 2, 'Red'),
        ( 3, 'Yellow')

--------------------------------------------------------------------
-- Declare A scroll cursor so you can output one row at a time
-- From your codeset table
--
-- Note: change your directory name below to your target directory
--------------------------------------------------------------------

    Declare @ID VARCHAR(MAX),@String varchar(max)
    Declare @Cursor as Cursor;

    Set @Cursor = Cursor For
    --One row with a filename, and the body of the document to be used in the cursor
    SELECT
        FileName = CONCAT(SnipLabel, '-', SnipGuid),
        DocumentBody = 
            CONCAT  
            (
                '{
                    "id": "', SnipGuid, '", 
                    "prefix": "', SnipLabel, '",
                    "description": "",
                    "body": "', SnipBody , '", 
                }'
            )
    FROM
        (
        SELECT
            SnipGuid = NEWID(),
            SnipLabel = CONCAT('enu_ColorType_', FORMAT(T.ColorTypeId, '00'), REPLACE(T.ColorTypeEnglishDescription, ' ', '')),
            SnipBody = CONCAT(T.ColorTypeId, ' -- ', REPLACE(T.ColorTypeEnglishDescription, ' ', ''))
            FROM
            #tblCodesColor T    -- <--- Your Codeset table here (And change corresponding field names)
        ) AS MyTable
    ORDER BY
        1

    Open @Cursor;
    Fetch Next From @Cursor into @ID, @String;

    --loop through the cursor and output one file per row
    While @@FETCH_STATUS = 0
    Begin
     Print cast(@ID as VARCHAR (max)) + ' ' + @String;
     Declare @Destination varchar(max) 
     Set @Destination = 'c:\temp\' +cast(@ID as varchar(max))+'.json'
     Exec #prc_Write_To_File @String,@Destination 
     Fetch Next From @Cursor Into @ID, @String;
    End

    Close @Cursor;
    Deallocate @Cursor;

------------------------------------
-- Results
------------------------------------

    --You will have three files with filenames and bodies similar to below:

        --enuColorType_03Yellow-A8AA870F-7F09-43BB-AB9A-32CEB4108529 
        --{
        --    "id": "A8AA870F-7F09-43BB-AB9A-32CEB4108529", 
        --    "prefix": "enuColorType_03Yellow",
        --    "description": "",
        --    "body": "3 -- Yellow", 
        --}

    --Typing this (or any fuzzy intelliSense part of this) in SSMS after adding the Snippet File
        SELECT * FROM #tblCodesColor CC WHERE cc.ColorTypeId = enu_colorType_01Blue 
    --Resolves to this
        SELECT * FROM #tblCodesColor CC WHERE cc.ColorTypeId = 1 -- Blue

Thanks for your time and have a great day,

Richard Gushue