Как известно, MSSQL не предоставляет возможность создания/использования перечислений, что зачастую влечет за собой неявный, ненадежный, некрасивый, сложно поддерживаемый код. Можно спорить о том, что в реляционной БД перечисления как таковые места иметь не могут, но множество раз в моей (и не только моей) практике рождались подобные строчки:
select * from Process where ProcessType = 1 /* Suspended */
или
declare @processSuspended int = 1;
select * from Process where ProcessType = @processSuspended;
------или------------------------------------------------------------------------------------------------------
DECLARE @processSuspended INT;
SELECT @processSuspended = Value FROM ProcessEnum WHERE Name = 'Suspended';
SELECT * FROM Process WHERE ProcessType = @processSuspended;
------или------------------------------------------------------------------------------------------------------
CREATE FUNCTION ProcessEnum_Suspended() RETURNS INT AS BEGIN RETURN 1; END;
SELECT * FROM Process WHERE ProcessType = ProcessEnum_Suspended();И тому подобные костыли разного уровня сложности.
А хочется —
SELECT * FROM Process WHERE ProcessType = EnumProcess.Suspended;Пытаемся максимально приблизиться к этому ласкающему взор синтаксису.
Disclaimer: Все дальнейшее писалось с использованием Visual Studio 2012, MS SQL Server 2012 SP1, .Net Framework 3.5, в сжатые сроки и является только примером, как.
Опробуем без лишних слов
1. Импортируем сборку (в конце статьи — ссылка на исходный код), (помним про ALTER DATABASE SET TRUSTWORTHY ON)
CREATE ASSEMBLY SQLAutoEnums...
CREATE ASSEMBLY [SQLAutoEnums] FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A240000000000000050450000648602004F6BFB510000000000000000F00022200B020B00003E000000040000000000000000000000200000000000800100000000200000000200000400000000000000040000000000000000800000000200000000000003004085000040000000000000400000000000000000100000000000002000000000000000000000100000000000000000000000000000000000000000600000B8020000000000000000000000000000000000000000000000000000FC5A00001C0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000002000004800000000000000000000002E74657874000000343C000000200000003E000000020000000000000000000000000000200000602E72737263000000B8020000006000000004000000400000000000000000000000000000400000402E72656C6F63000000000000008000000000000000440000000000000000000000000000400000424800000002000500142B0000E82F000001000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000133003006100000001000011000F00FE16040000016F0700000A28070000060A7E0800000A0B00066F1800000613041613052B1B110411059A0C0007087201000070280900000A0B00110517581305110511048E69FE04130611062DD7076F0A00000A730B00000A0D2B00092A000000133002003D00000002000011000F00FE16040000016F0700000A28070000060A066F1400000618FE0116FE010C082D0E066F16000006280C00000A0B2B0914280C00000A0B2B00072A0000001B3004004301000003000011007207000070730D00000A0A00066F0E00000A0072370000701B8D01000001130D110D16028C04000001A2110D17038C04000001A2110D18048C04000001A2110D19058C04000001A2110D1A0E048C04000001A2110D280F00000A0B0706731000000A130B110B176F1100000A00110B0C086F1200000A0D731300000A1304388F0000000009166F1400000A6F0700000A130509176F1400000A6F0700000A130609186F1400000A6F0700000A130709196F1500000A130811041105110628040000061309141109FE0116FE01130E110E2D2900731E000006130A110A11057D10000004110A11067D11000004110A1309110411096F1600000A000011097B1300000411071108731700000A6F1800000A0000096F1900000A130E110E3A62FFFFFF11042806000006130CDE120614FE01130E110E2D07066F1A00000A00DC00110C2A00411C0000020000000C000000210100002D01000012000000000000001B30020062000000040000110000026F1B00000A0C2B331202281C00000A0A00067B1100000404281D00000A2C11067B1000000403281D00000A16FE012B0117000D092D04060BDE22001202281E00000A0D092DC2DE0F1202FE160400001B6F1A00000A00DC00140B2B0000072A00000110000002000900424B000F00000000133003008B0000000500001100731E0000061304110472DC0000707D10000004110472E60000707D110000041104731F00000A7D1300000411040A067B1300000472F000007017731700000A6F1800000A00067B1300000472FE00007018731700000A6F1800000A00731300000A0B07066F1600000A0073200000060C08076F1F0000060D096F0A00000A730B00000A13052B0011052A00133002001F000000060000110073200000060A06026F1F0000060B076F0A00000A730B00000A0C2B00082A00133002001E000000070000110073110000060B07026F1300000600070A066F1A00000626060C2B00082A00001B300300A500000008000011007207000070730D00000A0A00066F0E00000A00720C01007002282000000A0B0706731000000A13071107176F1100000A0011070C086F1200000A0D732100000A13042B3800730B0000061306110609166F1500000A7D01000004110609176F1400000A6F0700000A7D0200000411061305110411056F2200000A0000096F1900000A130911092DBC11041308DE120614FE01130911092D07066F1A00000A00DC0011082A0000000110000002000C00838F001200000000133002001B0000000900001100000274030000020A03067B010000045404067B0200000451002A1E02282300000A2A1E02282300000A2A0013300400A60000000A00001102282300000A000002725C010070282400000A726A010070282500000A7D070000040272B6010070282400000A7D08000004021A8D010000010A0616027B08000004A2061772C0010070A20618282600000A8C1B000001A2061972C4010070A206282700000A7D09000004021A8D010000010A0616027B08000004A2061772C0010070A20618282600000A8C1B000001A2061972CC010070A206282700000A7D0A000004002A0000133001000C0000000B00001100027B030000040A2B00062A660002037D03000004021628150000060002142817000006002A0000133001000C0000000C00001100027B040000040A2B00062A260002037D040000042A0000133001000C0000000D00001100027B050000040A2B00062A260002037D050000042A0000133001000C0000000E00001100027B060000040A2B00062A260002037D060000042A00001B300500520100000F0000110002168D1000000128190000060002168D1C000001281700000600021728150000060000027B09000004022812000006282800000A00732900000A0A066F2A00000A027B070000046F2B00000A00066F2A00000A72D6010070027B0A000004027B09000004282C00000A6F2D00000A00066F2A00000A166F2E00000A00066F2A00000A176F2F00000A00066F2A00000A027B080000046F3000000A00066F3100000A26066F3200000A6F3300000A0B066F3400000A0002188D100000010D0916721A020070066F3500000A13041204283600000A282500000AA2091707A209281900000600066F3500000A16FE0116FE01130511052D1E0002027B0A000004283700000A2817000006000218281500000600002B0A00021928150000060000066F3500000A0CDE2726000219281500000600FE1A00027B09000004283800000A00027B0A000004283800000A0000DC00082A000041340000000000002300000005010000280100000C00000023000001020000002300000011010000340100001B00000000000000133001000B0000000B0000110072460200700A2B00062A0013300200170000000B00001100027B10000004027B11000004282500000A0A2B00062A0013300600690000001000001100027B120000042C15027B120000047E0800000A281D00000A16FE012B0116000B072D3A0002027B1100000472DC000070282600000A0C1202FE161B0000016F0700000A724E0200707E0800000A6F3900000A280900000A7D1200000400027B120000040A2B00062A4E02731F00000A7D1300000402282300000A002A0000001B300500AF0100001100001100733A00000A0A06027B140000046F3B00000A2600036F1B00000A130438530100001204281C00000A0B0006027B15000004076F1D000006076F1B0000066F3C00000A2600077B130000046F3D00000A13052B2A1205283E00000A0C0006027B160000041202283F00000A1202284000000A8C220000016F3C00000A26001205284100000A130611062DC9DE0F1205FE160600001B6F1A00000A00DC0006027B170000046F3B00000A2606027B1A000004076F1C000006076F1D0000066F3C00000A2600077B130000046F3D00000A13052B691205283E00000A0C0006027B18000004076F1C0000061202283F00000A076F1D0000066F4200000A2606027B190000041A8D010000011307110716076F1B000006A21107177252020070A21107181202283F00000AA2110719076F1D000006A211076F4300000A26001205284100000A130611062D8ADE0F1205FE160600001B6F1A00000A00DC0006027B1B000004076F1C000006076F1D0000066F3C00000A26001204281E00000A130611063A9DFEFFFFDE0F1204FE160400001B6F1A00000A00DC0006027B1C0000046F3B00000A26066F0700000A0D2B00092A00414C000002000000520000003B0000008D0000000F0000000000000002000000D10000007A0000004B0100000F00000000000000020000001D0000006A010000870100000F00000000000000033002006B000000000000000272560200707D140000040272BD0500707D150000040272F30500707D1600000402720B0600707D170000040272130600707D180000040272930600707D1900000402720F0700707D1A0000040272581200707D1B0000040272511A00707D1C00000402282300000A002A0042534A4201000100000000000C00000076322E302E35303732370000000005006C000000E0070000237E00004C0800009008000023537472696E677300000000DC100000581A000023555300342B0000100000002347554944000000442B0000A404000023426C6F620000000000000002000001571FA2090902000000FA2533001600000100000024000000080000001C000000200000001A0000000100000043000000040000000E00000011000000030000000A0000000F0000000600000001000000030000000100000000000A000100000000000600CA00C3000600D100C3000A00F700E2000A000001E2000A002101E20006006F0154010600D001BD010600780354010A00600445040600F204D3040600180505052F002C05000006005B053B0506007B053B050A00990545040600B705C3000A00F905E3050A001A0607060A003306E3050A003E0607060A004806D6000A006406E3050A00800607060600A806C3001B00BC0600000600F606C30006001907C30006002607C300060035072B070E00470705050E004F0705050600D3072B070600F3072B0706002108C30006003B08C300060059084D080000000001000000000001000100010010001B0000000500010001000300100030000000050001000B00A00000003B004500000003000C000000100052005E000500030011000101000075005E0009000B001B000000100083009200050010001B0000001000AA009200050014001F0006000F025D00060012026000010066026000010077028E000100900298000100C802A7000100DA0260000100E30260000100ED0260000100F7026000060611035D00568019038E00568025038E00568030038E00568038038E0006004D03600006001202600001006E03600006008703C4000100B30360000100BE0360000100CD0360000100DA0360000100E90360000100F903600001001004600001002504600001003A04600050200000000096000A010A000100C0200000000096002B01110003000C210000000096003F01180005007822000000009100760127000B00F8220000000096007D0134000E009023000000009100960139000F00BC23000000009100B30144001000E823000000009600DC014A001100AC24000000009600EF0150001200D324000000008618090259001500DB24000000008618090259001500000000000000C60D170263001500000000000000C60D200267001500000000000000C60529026C001600000000000000C60D310270001600000000000000C60D3C0275001600E424000000008618090259001600982500000000E609170263001600B02500000000E609200267001600CC2500000000E609310270001700E4250000000081086C0288001700F02500000000E6093C027500180008260000000081087F029200180014260000000086089E029C0019002C26000000008108B302A1001900382600000000E60129026C001A00CC270000000086083F0363001A00E427000000008608540363001A000828000000008608610363001A007D28000000008618090259001A009428000000008600AA03D0001A009C2A000000008618090259001B00000000000000000001007204000000000000000001007204000000000000000001007704000002008104000003008E0400000400990400000500A60400000100B20400000200B70400000300BE0400000000000000000100B20400000100720400000100C30400000100CC0402000200D00402000300BE0400000100FF0400000100FF0400000100FF0400000100FF0400000100FF0400000100B2040500100049000902590051000902590059000902ED0069000902F3007100090259007900090259000900AE0563008100BE0560008100C405F8008100CB05FF001900090204012900D705170189000902670091002706590081002C06B70199000902BE01A1005406C50199007206CB010C0009025900B9008D06D701B9009606DC010C009F06E10114000902EE011C009F06E101B900A3060102C100B40659000C00C70624022400D50634028100E10639022400ED0601021C000902590081002C0685032C00090259002C009F06E101090009025900D1000207B0038100C405B503D9001E07BB038100C405C003E9003A07DE03F10009025900F1006007E403F9006E07670081002C06E903F9007B076700F9008907F003F9009D07F003F900B8076700F100CD070102F100E007F5030901FE076300F10008085900F10014086C001101AE056300E9002708FB03E90034080104810045081104210109025900210167081E0421016E0825041C00C70624023400D506340214007B0834021400830839043400ED06010221016E083E0421016E08480408003000B00008003400B50008003800BA0008003C00BF0020003300B50024000B00DA002E002B0085042E001B0073042E0023007C0440003300B50044000B00DA0060003300260164000B00DA0084000B00DA00A0003300B500A4000B00DA00000133007002C4010B00DA000A011E0105023F024D025F0267029203AB03C603CB03CF03D403D90306041704510404000100050004000700080000004D027A00000052027E0000005902830000004D027A00000052027E0000005902830000000003AB0000008E037A00000098037A000000A1037A0002000C00030001000D00030002000F000500020010000700020012000900010013000900010015000B00020014000B00020016000D00010017000D00020018000F00010019000F0002001B00110002001C00130002001D001500D001E701F6012D028B032E04048000000000000000000000000000000000450000000200000000000000000000000100BA00000000000200000000000000000000000100D600000000000200000000000000000000000100C30000000000030002000000003C4D6F64756C653E0053514C4175746F456E756D732E646C6C0055736572446566696E656446756E6374696F6E7300456E756D4D656D6265720049436F6D70696C65720053514C4175746F456E756D7300437363436F6D70696C65720053514C4175746F456E756D732E436F6D70696C65727300436F6D70696C6553746174757300456E756D44657363726970746F720053514C4175746F456E756D732E47656E657261746F72730053696D706C6547656E657261746F72006D73636F726C69620053797374656D004F626A65637400456E756D0053797374656D2E446174610053797374656D2E446174612E53716C54797065730053716C43686172730053716C537472696E670053716C4175746F456E756D73547279436F6D70696C650053716C42696E6172790053716C4175746F456E756D73436F6D70696C650053716C4175746F456E756D7347656E65726174650053797374656D2E436F6C6C656374696F6E732E47656E65726963004C6973746031005365617263680053716C4175746F456E756D7347656E6572617465546573740053716C4175746F456E756D7347656E657261746546726F6D4C69737400436F6D70696C6549740053797374656D2E436F6C6C656374696F6E730049456E756D657261626C6500456E756D4D656D6265727343757272656E7400456E756D4D656D6265727343757272656E7446696C6C526F77002E63746F72004944004E616D65006765745F436F6465007365745F436F646500436F6D70696C65006765745F537461747573006765745F436F6D70696C6564436F646500436F64650053746174757300436F6D70696C6564436F6465005F636F6465007365745F537461747573005F737461747573007365745F436F6D70696C6564436F6465005F636F6D70696C6564436F6465006765745F436F6D70696C65724D65737361676573007365745F436F6D70696C65724D65737361676573005F636F6D70696C65724D65737361676573005F63736350617468005F74656D7050617468005F636F646550617468005F646C6C5061746800436F6D70696C65724D657373616765730076616C75655F5F004E6F74436F6D70696C656400496E50726F67726573730053756363657373004661696C6564006765745F56616C75655479706500507265666978006765745F4E616D6546756C6C006765745F4E616D65456E756D005F6E616D65456E756D004B657956616C75655061697260320056616C7565730056616C756554797065004E616D6546756C6C004E616D65456E756D0047656E6572617465005F737472486561646572005F737472456E756D486561646572005F737472456E756D4974656D005F737472456E756D466F6F746572005F73747253747275637456616C7565005F7374725374727563744E756D6572696356616C7565005F737472456E756D537472756374486561646572005F737472456E756D537472756374466F6F746572005F737472466F6F746572004D6963726F736F66742E53716C5365727665722E5365727665720053716C466163657441747472696275746500636F6465007461626C654E616D6500636F6C756D6E50726566697800636F6C756D6E4E616D6500636F6C756D6E4D656D62657200636F6C756D6E56616C7565006C69737400707265666978006E616D6500656E756D4E616D65006F626A0069640053797374656D2E52756E74696D652E496E7465726F705365727669636573004F75744174747269627574650076616C75650053797374656D2E446961676E6F73746963730044656275676761626C6541747472696275746500446562756767696E674D6F6465730053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C6974794174747269627574650053716C46756E6374696F6E41747472696275746500546F537472696E6700537472696E6700456D70747900436F6E63617400546F436861724172726179006F705F496D706C696369740053797374656D2E446174612E53716C436C69656E740053716C436F6E6E656374696F6E0053797374656D2E446174612E436F6D6D6F6E004462436F6E6E656374696F6E004F70656E00466F726D61740053716C436F6D6D616E64004462436F6D6D616E6400436F6D6D616E6454797065007365745F436F6D6D616E64547970650053716C44617461526561646572004578656375746552656164657200446244617461526561646572006765745F4974656D00476574496E7433320041646400526561640049446973706F7361626C6500446973706F736500456E756D657261746F7200476574456E756D657261746F72006765745F43757272656E74006F705F457175616C697479004D6F76654E65787400456E7669726F6E6D656E7400476574456E7669726F6E6D656E745661726961626C650047756964004E65774775696400427974650053797374656D2E494F0046696C65005772697465416C6C546578740050726F636573730050726F636573735374617274496E666F006765745F5374617274496E666F007365745F46696C654E616D65007365745F417267756D656E7473007365745F5573655368656C6C45786563757465007365745F52656469726563745374616E646172644F7574707574007365745F576F726B696E674469726563746F72790053746172740053747265616D526561646572006765745F5374616E646172644F757470757400546578745265616465720052656164546F456E640057616974466F7245786974006765745F45786974436F646500496E7433320052656164416C6C42797465730044656C65746500457863657074696F6E005265706C6163650053797374656D2E5465787400537472696E674275696C64657200417070656E6400417070656E64466F726D6174006765745F4B6579006765745F56616C75650000000000050D000A00002F63006F006E007400650078007400200063006F006E006E0065006300740069006F006E003D0074007200750065000080A3730065006C0065006300740020007B0031007D0020006100730020005000720065006600690078002C0020007B0032007D0020006100730020004E0061006D0065002C0020007B0033007D0020006100730020004D0065006D006200650072004E0061006D0065002C0020007B0034007D0020006100730020004D0065006D00620065007200560061006C00750065002000660072006F006D0020007B0030007D00000945006E0075006D0000095400650073007400000D45006E00760061006C003100000D45006E00760061006C003200004F730065006C006500630074002000490044002C0020004E0061006D0065002000660072006F006D002000640062006F002E005B007B0030007D002E0054006F004C006900730074005D0028002900000D770069006E00640069007200004B5C004D006900630072006F0073006F00660074002E004E00450054005C004600720061006D00650077006F0072006B005C00760033002E0035005C006300730063002E006500780065000009540045004D00500000035C0000072E006300730000092E0064006C006C0000432F007400610072006700650074003A006C0069006200720061007200790020002F006F00750074003A0022007B0030007D002200200022007B0031007D0022002000002B43006F006D00700069006C006500720020006500780069007400200063006F006400650020003D002000000769006E00740000032D00010369000083650D000A002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020007500730069006E0067002000530079007300740065006D003B0020000D000A002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020007500730069006E0067002000530079007300740065006D002E0044006100740061002E00530071006C00540079007000650073003B0020000D000A002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020007500730069006E00670020004D006900630072006F0073006F00660074002E00530071006C005300650072007600650072002E005300650072007600650072003B0020000D000A002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020007500730069006E0067002000530079007300740065006D002E0043006F006C006C0065006300740069006F006E0073003B000D000A002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020007500730069006E0067002000530079007300740065006D002E0043006F006C006C0065006300740069006F006E0073002E00470065006E0065007200690063003B000D000A002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020006E0061006D006500730070006100630065002000530071006C004100750074006F0045006E0075006D007300470065006E0065007200610074006500640020000D000A002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020007B00200000357000750062006C0069006300200065006E0075006D0020007B0030007D0020003A0020007B0031007D0020007B007B002000200000177B0030007D0020003D0020007B0031007D002C002000000720007D002000007F7000750062006C0069006300200073007400610074006900630020007B0030007D0020007B0031007D0020007B007B00200067006500740020007B007B002000720065007400750072006E0020006E006500770020007B0030007D0028007B0032007D002E007B0031007D0029003B0020007D007D0020007D007D002000007B7000750062006C0069006300200073007400610074006900630020007B0030007D0020007B0031007D007B0032007D0020007B007B00200067006500740020007B007B002000720065007400750072006E00200028007B0030007D0029007B0033007D002E007B0032007D003B0020007D007D0020007D007D00008B470D000A0020002000200020005B00530065007200690061006C0069007A00610062006C0065005D000D000A0020002000200020005B00530071006C00550073006500720044006500660069006E00650064005400790070006500280046006F0072006D00610074002E00550073006500720044006500660069006E00650064002C0020004900730042007900740065004F0072006400650072006500640020003D00200074007200750065002C0020004D00610078004200790074006500530069007A0065003D002D00310029005D0020000D000A0020002000200020007000750062006C0069006300200073007400720075006300740020007B0030007D0020003A00200049004E0075006C006C00610062006C0065002C002000200020004900420069006E00610072007900530065007200690061006C0069007A0065000D000A0020002000200020007B007B000D000A00200020002000200020002000200020007000750062006C006900630020007B0030007D0028007B0031007D002000760061006C0029000D000A00200020002000200020002000200020007B007B000D000A002000200020002000200020002000200020002000200020005F00760061006C007500650020003D002000760061006C003B0020005F006E0075006C006C0020003D002000660061006C00730065003B000D000A00200020002000200020002000200020007D007D000D000A000D000A00200020002000200020002000200020007000750062006C006900630020007B0031007D003F002000560061006C00750065000D000A00200020002000200020002000200020007B007B000D000A0020002000200020002000200020002000200020002000200067006500740020007B007B002000720065007400750072006E0020005F00760061006C00750065003B0020007D007D000D000A0020002000200020002000200020002000200020002000200073006500740020007B007B0020005F00760061006C007500650020003D002000760061006C00750065003B0020005F006E0075006C006C0020003D0020002800760061006C007500650020003D003D0020006E0075006C006C0029003B0020007D007D0020000D000A00200020002000200020002000200020007D007D000D000A0020002000200020002000200020002000700072006900760061007400650020007B0031007D003F0020005F00760061006C00750065003B000D000A00200020002000200020002000200020007000720069007600610074006500200062006F006F006C0020005F006E0075006C006C003B000D000A00200020002000200020002000200020007000750062006C006900630020006F007600650072007200690064006500200073007400720069006E006700200054006F0053007400720069006E006700280029000D000A00200020002000200020002000200020007B007B000D000A00200020002000200020002000200020002000200020002000720065007400750072006E002000560061006C007500650020003D003D0020006E0075006C006C0020003F00200073007400720069006E0067002E0045006D0070007400790020003A00200045006E0075006D002E004700650074004E0061006D006500280074007900700065006F00660028007B0031007D0029002C002000560061006C007500650029003B000D000A00200020002000200020002000200020007D007D000D000A00200020002000200020002000200020007000750062006C0069006300200069006E0074003F00200054006F0049006E007400280029000D000A00200020002000200020002000200020007B007B000D000A00200020002000200020002000200020002000200020002000720065007400750072006E002000560061006C007500650020003D003D0020006E0075006C006C0020003F002000280069006E0074003F0029006E0075006C006C0020003A002000280069006E0074002900560061006C00750065002E00560061006C00750065003B000D000A00200020002000200020002000200020007D007D000D000A00200020000D000A000D000A000D000A000D000A00200020002000200020002000200020005B00530071006C00460075006E006300740069006F006E002800460069006C006C0052006F0077004D006500740068006F0064004E0061006D00650020003D002000220054006F004C00690073007400460069006C006C0052006F00770022002C0020005400610062006C00650044006500660069006E006900740069006F006E003D00220049004400200049004E0054002C0020004E0061006D00650020006E007600610072006300680061007200280034003000300030002900220029005D00200020000D000A00200020002000200020002000200020007000750062006C006900630020007300740061007400690063002000490045006E0075006D0065007200610062006C006500200054006F004C006900730074002800290020000D000A00200020002000200020002000200020007B007B0020000D000A0020002000200020002000200020002000200020002000200076006100720020006C007300740020003D0020006E006500770020004C006900730074003C004B0065007900560061006C007500650050006100690072003C0069006E0074002C00200073007400720069006E0067003E003E00280029003B0020000D000A0020002000200020002000200020002000200020002000200066006F00720065006100630068002000280076006100720020006400610074006100200069006E00200045006E0075006D002E00470065007400560061006C00750065007300280074007900700065006F00660028007B0031007D0029002900290020000D000A0020002000200020002000200020002000200020002000200020002000200020006C00730074002E0041006400640028006E006500770020004B0065007900560061006C007500650050006100690072003C0069006E0074002C00200073007400720069006E0067003E002800280069006E0074002900200064006100740061002C002000280028007B0031007D0029002000640061007400610029002E0054006F0053007400720069006E00670028002900290029003B0020000D000A00200020002000200020002000200020002000200020002000720065007400750072006E0020006C00730074003B0020000D000A00200020002000200020002000200020007D007D0020000D000A0020000D000A00200020002000200020002000200020007000750062006C00690063002000730074006100740069006300200076006F0069006400200054006F004C00690073007400460069006C006C0052006F00770028004F0062006A0065006300740020006F0062006A002C0020006F0075007400200069006E0074002000690064002C0020006F0075007400200073007400720069006E00670020006E0061006D006500290020000D000A00200020002000200020002000200020007B007B000D000A002000200020002000200020002000200020002000200020007600610072002000640061007400610020003D00200028004B0065007900560061006C007500650050006100690072003C0069006E0074002C00200073007400720069006E0067003E0029006F0062006A003B000D000A002000200020002000200020002000200020002000200020006900640020003D00200064006100740061002E004B00650079003B000D000A002000200020002000200020002000200020002000200020006E0061006D00650020003D00200064006100740061002E00560061006C00750065003B0020000D000A00200020002000200020002000200020007D007D0020000D000A000D000A000D000A000D000A000D000A000D000A000D000A000D000A000D000A00200020002000200020002000200020000187F70D000A00200020002000200020002000200020007000750062006C00690063002000730074006100740069006300200062006F006F006C0020006F00700065007200610074006F00720020003D003D0028007B0030007D00200061002C0020007B0030007D0020006200290020007B007B002000720065007400750072006E00200061002E00560061006C007500650020003D003D00200062002E00560061006C00750065003B0020007D007D000D000A00200020002000200020002000200020007000750062006C00690063002000730074006100740069006300200062006F006F006C0020006F00700065007200610074006F007200200021003D0028007B0030007D00200061002C0020007B0030007D0020006200290020007B007B002000720065007400750072006E00200061002E00560061006C0075006500200021003D00200062002E00560061006C00750065003B0020007D007D000D000A00200020002000200020002000200020007000750062006C0069006300200062006F006F006C002000490073004E0075006C006C0020007B007B00200067006500740020007B007B002000720065007400750072006E0020005F006E0075006C006C003B0020007D007D0020007D007D000D000A00200020002000200020002000200020007000750062006C0069006300200073007400610074006900630020007B0030007D0020004E0075006C006C0020007B007B00200067006500740020007B007B0020007600610072002000680020003D0020006E006500770020007B0030007D0020007B007B0020005F006E0075006C006C0020003D002000740072007500650020007D007D003B002000720065007400750072006E00200068003B0020007D007D0020007D007D000D000A000D000A00200020002000200020002000200020007000750062006C0069006300200073007400610074006900630020007B0030007D002000500061007200730065002800530071006C0053007400720069006E0067002000730029000D000A00200020002000200020002000200020007B007B000D000A00200020002000200020002000200020002000200020002000690066002000280073002E00490073004E0075006C006C0029002000720065007400750072006E0020004E0075006C006C003B000D000A002000200020002000200020002000200020002000200020007600610072002000750020003D0020006E006500770020007B0030007D000D000A0020002000200020002000200020002000200020002000200020002000200020007B007B002000560061006C007500650020003D00200028007B0031007D00290045006E0075006D002E0050006100720073006500280074007900700065006F00660028007B0031007D0029002C00200073002E0054006F0053007400720069006E00670028002900290020007D007D003B000D000A00200020002000200020002000200020002000200020002000720065007400750072006E00200075003B000D000A00200020002000200020002000200020007D007D000D000A000D000A00200020002000200020002000200020007000750062006C0069006300200076006F00690064002000570072006900740065002800530079007300740065006D002E0049004F002E00420069006E0061007200790057007200690074006500720020007700290020000D000A00200020002000200020002000200020007B007B0020000D000A00200020002000200020002000200020002000200020002000690066002000280074006800690073002E00490073004E0075006C006C00290020000D000A002000200020002000200020002000200020002000200020007B007B0020000D000A00200020002000200020002000200020002000200020002000200020002000200077002E00570072006900740065002800280069006E00740029002D00310029003B0020000D000A002000200020002000200020002000200020002000200020002000200020002000720065007400750072006E003B0020000D000A002000200020002000200020002000200020002000200020007D007D0020000D000A0020002000200020002000200020002000200020002000200077002E00570072006900740065002800280069006E00740029005F00760061006C007500650029003B0020000D000A00200020002000200020002000200020007D007D0020000D000A00090009007000750062006C0069006300200076006F0069006400200052006500610064002800530079007300740065006D002E0049004F002E00420069006E0061007200790052006500610064006500720020007200290020000D000A00200020002000200020002000200020007B007B0020000D000A0020002000200020002000200020002000200020002000200069006E0074002000640061007400610020003D00200072002E00520065006100640049006E00740033003200280029003B0020000D000A002000200020002000200020002000200020002000200020006900660020002800640061007400610020003D003D0020002D0031002900200074006800690073002E005F006E0075006C006C0020003D00200074007200750065003B0020000D000A0009000900090065006C007300650020002000200074006800690073002E00560061006C007500650020003D00200028007B0031007D00290064006100740061003B0020000D000A00200020002000200020002000200020007D007D0020000D000A0020002000200020007D007D0020000D000A0001037D00000000006D24D8A2871C8F4490831DEDE56E22D30008B77A5C561934E089060001120D1111060001111511110E0005120D111111111111111111110C0003121C15121901121C0E0E040000120D0A0001120D15121901121C05000112140E050001121D0E080003011C1008100E0320000102060802060E0320000E042001010E0320000804200011180420001D050328000E04280011180428001D0505200101111803061118052001011D0503061D050420001D0E052001011D0E03061D0E0428001D0E04000000000401000000040200000004030000000B0615121901151121020E080920010E15121901121C12010001005408074D617853697A65FFFFFFFF05200101113104200101080600030E0E0E0E0420001D03052001011D030C070712140E0E120D1D0E080206000111151D050707031214111502808F010001005455794D6963726F736F66742E53716C5365727665722E5365727665722E446174614163636573734B696E642C2053797374656D2E446174612C2056657273696F6E3D322E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D623737613563353631393334653038390A44617461416363657373010000000600020E0E1D1C062002010E124505200101115504200012590615121901121C0420011C08042001080805200101130006151121020E0807200201130013010A15121901151121020E08032000021E070F12450E124D125915121901121C0E0E0E08121C121C124D120D1D1C020820001511650113000615116501121C0420001300050002020E0E0D0704121C121C15116501121C02110706121C15121901121C12200E121C120D07070312200E120D0807031214121412148113010004005455794D6963726F736F66742E53716C5365727665722E5365727665722E446174614163636573734B696E642C2053797374656D2E446174612C2056657273696F6E3D322E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D623737613563353631393334653038390A4461746141636365737301000000540E044E616D652053716C4175746F456E756D732E456E756D4D656D626572735F43757272656E74540E1146696C6C526F774D6574686F644E616D6519456E756D4D656D6265727343757272656E7446696C6C526F77540E0F5461626C65446566696E6974696F6E1B494420494E542C204E616D65206E766172636861722834303030290500020E0E1C0615121901120C18070A12450E124D125915121901120C120C120C124D121D02040701120C0400010E0E0500020E0E0E040000116D0500010E1D1C0407011D1C0307010E04070111180407011D050407011D0E050002010E0E042000127D0600030E0E1C1C04200101020520001280810500011D050E040001010E0A070612790E081D0E08020520020E0E0E0607030E02116D0620011280910E0820031280910E1C1C0A15116501151121020E0804200013010920041280910E1C1C1C0820021280910E1D1C210708128091121C151121020E080E15116501121C15116501151121020E08021D1C0801000701000000000801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F777301000000004F6BFB5100000000020000001C010000185B0000183D00005253445374E02C9A86908949B33A077403A171D801000000633A5C576F726B735C53514C4175746F456E756D735C53514C4175746F456E756D735C6F626A5C44656275675C53514C4175746F456E756D732E7064620000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001001000000018000080000000000000000000000000000001000100000030000080000000000000000000000000000001000000000048000000586000005C02000000000000000000005C0234000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE00000100000000000000000000000000000000003F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B004BC010000010053007400720069006E006700460069006C00650049006E0066006F0000009801000001003000300030003000300034006200300000002C0002000100460069006C0065004400650073006300720069007000740069006F006E000000000020000000300008000100460069006C006500560065007200730069006F006E000000000030002E0030002E0030002E003000000044001100010049006E007400650072006E0061006C004E0061006D0065000000530051004C004100750074006F0045006E0075006D0073002E0064006C006C00000000002800020001004C006500670061006C0043006F0070007900720069006700680074000000200000004C00110001004F0072006900670069006E0061006C00460069006C0065006E0061006D0065000000530051004C004100750074006F0045006E0075006D0073002E0064006C006C0000000000340008000100500072006F006400750063007400560065007200730069006F006E00000030002E0030002E0030002E003000000038000800010041007300730065006D0062006C0079002000560065007200730069006F006E00000030002E0030002E0030002E00300000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 WITH PERMISSION_SET = UNSAFE
GO
CREATE FUNCTION [dbo].[SqlAutoEnums.EnumMembers_Current](@enumName [nvarchar](4000)) RETURNS TABLE ( [ID] [int] NULL, [Name] [nvarchar](4000) NULL ) WITH EXECUTE AS N'dbo' AS EXTERNAL NAME [SQLAutoEnums].[UserDefinedFunctions].[EnumMembersCurrent]
GO
CREATE FUNCTION [dbo].[SqlAutoEnumsCompile](@code [nvarchar](max)) RETURNS [varbinary](max) WITH EXECUTE AS CALLER AS EXTERNAL NAME [SQLAutoEnums].[UserDefinedFunctions].[SqlAutoEnumsCompile]
GO
CREATE FUNCTION [dbo].[SqlAutoEnumsGenerate](@tableName [nvarchar](4000), @columnPrefix [nvarchar](4000), @columnName [nvarchar](4000), @columnMember [nvarchar](4000), @columnValue [nvarchar](4000)) RETURNS [nvarchar](max) WITH EXECUTE AS CALLER AS EXTERNAL NAME [SQLAutoEnums].[UserDefinedFunctions].[SqlAutoEnumsGenerate]
GO
CREATE FUNCTION [dbo].[SqlAutoEnumsTryCompile](@code [nvarchar](max)) RETURNS [nvarchar](max) WITH EXECUTE AS CALLER AS EXTERNAL NAME [SQLAutoEnums].[UserDefinedFunctions].[SqlAutoEnumsTryCompile]
GO
2. Создаем тестовую таблицу SqlAutoEnums.Data, в которой у нас будет лежать описание перечислений и view, смотрящую на нее (позднее можно с сохранением структуры перенаправить ее на другую таблицу, где у вас хранятся перечисления, тогда таблица SqlAutoEnums.Data не нужна):
CREATE TABLE dbo.[SqlAutoEnums.Data]...
CREATE TABLE [dbo].[SqlAutoEnums.Data](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Prefix] [nvarchar](50) NOT NULL,
[Name] [nvarchar](50) NOT NULL,
[MemberName] [nvarchar](50) NOT NULL,
[MemberValue] [int] NOT NULL,
CONSTRAINT [PK_SqlAutoEnums.Data] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_SqlAutoEnums.Data] ON [dbo].[SqlAutoEnums.Data]
(
[Prefix] ASC,
[Name] ASC,
[MemberName] ASC,
[MemberValue] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_SqlAutoEnums.Data.A] ON [dbo].[SqlAutoEnums.Data]
(
[Name] ASC,
[MemberValue] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE VIEW [dbo].[SqlAutoEnums.Data.View]
AS
SELECT ID ,
Prefix ,
Name ,
MemberName ,
MemberValue
FROM dbo.[SqlAutoEnums.Data]
3. Кучка утилитарно-вспомогательных функций:
CREATE FUNCTION...
CREATE FUNCTION [dbo].[SqlAutoEnums.Enum_Equals]
(
@enumName NVARCHAR(100)
)
RETURNS bit
AS
BEGIN
DECLARE @res BIT = 0;
SET @res = CASE
WHEN
EXISTS (
SELECT Name AS MemberName, ID AS MemberValue FROM dbo.[SqlAutoEnums.EnumMembers_Current](@enumName)
except
select MemberName, MemberValue FROM dbo.[SqlAutoEnums.EnumMembers_New](@enumName)
)
OR
EXISTS (
SELECT MemberName, MemberValue FROM dbo.[SqlAutoEnums.EnumMembers_New](@enumName)
EXCEPT
SELECT Name AS MemberName, ID AS MemberValue FROM dbo.[SqlAutoEnums.EnumMembers_Current](@enumName)
)
THEN 0
ELSE 1
END;
RETURN @res;
END
GO
--=================================================================================================================
CREATE FUNCTION [dbo].[SqlAutoEnums.Enum_HasDependencies]
(
@schemaName NVARCHAR(100),
@typeName NVARCHAR(100),
@onColumns BIT,
@oncomputedColumns BIT,
@onParameters BIT,
@onCheckConstraints BIT,
@onCode BIT
)
RETURNS bit
AS
BEGIN
DECLARE @res BIT = 0;
DECLARE @typeidname NVARCHAR(255) = '[' + @schemaName + '].[' + @typeName + ']';
IF (@onColumns = 1)
BEGIN
SET @res = CASE WHEN EXISTS (
SELECT 1 --OBJECT_NAME(object_id) AS object_name ,c.name AS column_name ,SCHEMA_NAME(t.schema_id) AS schema_name,TYPE_NAME(c.user_type_id) AS user_type_name,c.max_length,c.precision,c.scale,c.is_nullable,c.is_computed
FROM sys.columns AS c
INNER JOIN sys.types AS t ON c.user_type_id = t.user_type_id
WHERE c.user_type_id = TYPE_ID(@typeidname)
) THEN 1
END;
IF (1 = @res) RETURN @res;
END;
IF (@oncomputedColumns = 1)
BEGIN
SET @res = CASE WHEN EXISTS (
SELECT 1 --OBJECT_NAME(object_id) AS OBJECT_NAME ,COL_NAME(object_id, column_id) AS column_name
FROM sys.sql_dependencies
WHERE referenced_major_id = TYPE_ID(@typeidname) AND
class = 2 AND -- schema-bound references to type
OBJECTPROPERTY(object_id, 'IsTable') = 1
) THEN 1
END;
IF (1 = @res) RETURN @res;
END;
IF (@onParameters = 1)
BEGIN
SET @res = CASE WHEN EXISTS (
SELECT 1 -- OBJECT_NAME(object_id) AS object_name ,NULL AS procedure_number ,name AS param_name ,parameter_id AS param_num ,TYPE_NAME(p.user_TYPE_ID) AS type_name
FROM sys.parameters AS p
WHERE p.user_TYPE_ID = TYPE_ID(@typeidname)
UNION
SELECT 1 -- OBJECT_NAME(object_id) AS object_name ,procedure_number ,name AS param_name ,parameter_id AS param_num ,TYPE_NAME(p.user_TYPE_ID) AS type_name
FROM sys.numbered_procedure_parameters AS p
WHERE p.user_TYPE_ID = TYPE_ID(@typeidname)
) THEN 1
END;
IF (1 = @res) RETURN @res;
END;
IF (@onCheckConstraints = 1)
BEGIN
SET @res = CASE WHEN EXISTS (
SELECT 1 -- SCHEMA_NAME(o.schema_id) AS schema_name ,OBJECT_NAME(o.parent_object_id) AS table_name ,OBJECT_NAME(o.object_id) AS constraint_name
FROM sys.sql_dependencies AS d
JOIN sys.objects AS o ON o.object_id = d.object_id
WHERE referenced_major_id = TYPE_ID(@typeidname) AND
class = 2 AND -- schema-bound references to type
OBJECTPROPERTY(o.object_id, 'IsCheckCnst') = 1 -- exclude non-CHECK dependencies
) THEN 1
END;
IF (1 = @res) RETURN @res;
END;
IF (@onCode = 1)
BEGIN
SET @res = CASE WHEN EXISTS (
SELECT 1 -- SCHEMA_NAME(o.schema_id) AS dependent_object_schema ,OBJECT_NAME(o.object_id) AS dependent_object_name ,o.type_desc AS dependent_object_type ,d.class_desc AS kind_of_dependency ,TYPE_NAME (d.referenced_major_id) AS type_name
FROM sys.sql_dependencies AS d
JOIN sys.objects AS o ON d.object_id = o.object_id AND o.type IN ('FN','IF','TF', 'V', 'P')
WHERE d.class = 2 AND -- dependencies on types
d.referenced_major_id = TYPE_ID(@typeidname)
) THEN 1
END;
IF (1 = @res) RETURN @res;
END;
RETURN 0;
END
GO
--=================================================================================================================
CREATE FUNCTION [dbo].[SqlAutoEnums.EnumMembers_New]
(
@enumName NVARCHAR(100)
)
RETURNS TABLE
AS
RETURN
(
-- select * from [SqlAutoEnums.NewEnumVals]()
SELECT Prefix+Name AS EnumName,
MemberName AS MemberName,
MemberValue AS MemberValue
FROM dbo.[SqlAutoEnums.Data.View]
WHERE Prefix+Name = @enumName
)
GO
--=================================================================================================================
CREATE FUNCTION [dbo].[SqlAutoEnums.Enums_Current]()
RETURNS TABLE
AS
RETURN
(
SELECT atold.assembly_id AS AssemblyId,
asm.name AS AssemblyName,
atold.user_type_id AS EnumId,
atold.name AS EnumName
FROM sys.assembly_types atold
INNER JOIN sys.assemblies asm on asm.name LIKE 'SQLAutoEnums.Generated%' AND atold.assembly_id = asm.assembly_id
)
GO
--======================================================================================================================
CREATE FUNCTION [dbo].[SqlAutoEnums.Enums_New]()
RETURNS TABLE
AS
RETURN
(
SELECT DISTINCT Prefix+Name AS EnumName
FROM dbo.[SqlAutoEnums.Data.View]
)
GO
--=================================================================================================================
CREATE FUNCTION [dbo].[SqlAutoEnums.EnumsMembers_Current]
(
)
RETURNS TABLE
AS
RETURN
(
SELECT e.AssemblyId, e.AssemblyName, e.EnumId, e.EnumName,
v.Name AS MemberName, v.ID AS MemberValue
FROM (
SELECT AssemblyId, AssemblyName, EnumId, CAST(EnumName AS NVARCHAR(100)) AS EnumName
FROM dbo.[SqlAutoEnums.Enums_Current]()
) e
CROSS APPLY dbo.[SqlAutoEnums.EnumMembers_Current](e.EnumName) AS v
)
GO
--=================================================================================================================
CREATE FUNCTION [dbo].[SqlAutoEnums.EnumsMembers_New]
(
)
RETURNS TABLE
AS
RETURN
(
SELECT Prefix+Name AS EnumName,
MemberName AS MemberName,
MemberValue AS MemberValue
FROM dbo.[SqlAutoEnums.Data.View]
)
GO
4. Делаем процедуру для создания/обновления перечислений
CREATE PROCEDURE dbo.[SqlAutoEnums.Renew]...
CREATE PROCEDURE [dbo].[SqlAutoEnums.Renew]
WITH EXECUTE AS SELF
AS
BEGIN
DECLARE @msg NVARCHAR(MAX);
BEGIN TRY
BEGIN TRAN
--============================================================================================================
-- dropping current enums
PRINT 'Current enums: clearing...';
IF (EXISTS (
SELECT 1
FROM dbo.[SqlAutoEnums.Enums_Current]() ec
WHERE dbo.[SqlAutoEnums.Enum_Equals](ec.EnumName) = 0 AND
dbo.[SqlAutoEnums.Enum_HasDependencies]('', ec.EnumName, 1, 1, 1, 1, 1) = 1
))
BEGIN
SET @msg = 'Cannot modify or drop enums cause of dependencies: ';
SELECT @msg += ec.EnumName + ', '
FROM dbo.[SqlAutoEnums.Enums_Current]() ec
WHERE dbo.[SqlAutoEnums.Enum_Equals](ec.EnumName) = 0 AND
dbo.[SqlAutoEnums.Enum_HasDependencies]('', ec.EnumName, 1, 1, 1, 1, 1) = 1
SET @msg = SUBSTRING(@msg, 1, LEN(@msg)-2);
RAISERROR(@msg, 16, 2);
END;
--assembly list to drop
DECLARE @asstodrop TABLE (Name NVARCHAR(MAX));
INSERT INTO @asstodrop (Name)
SELECT ec.AssemblyName
FROM dbo.[SqlAutoEnums.Enums_Current]() ec
GROUP BY ec.AssemblyName
HAVING SUM(CAST(dbo.[SqlAutoEnums.Enum_HasDependencies]('', ec.EnumName, 1, 1, 1, 1, 1) AS INT)) = 0
-- dropping enums
DECLARE @qryDropEnum NVARCHAR(MAX);
DECLARE @qryDropEnumToList NVARCHAR(MAX);
DECLARE @oldEnumName NVARCHAR(MAX);
DECLARE enumCursor CURSOR FOR
SELECT ec.EnumName
FROM dbo.[SqlAutoEnums.Enums_Current]() ec
WHERE dbo.[SqlAutoEnums.Enum_HasDependencies]('', ec.EnumName, 1, 1, 1, 1, 1) = 0
OPEN enumCursor;
FETCH NEXT FROM enumCursor INTO @oldEnumName;
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT ' Dropping enum ' + @oldEnumName;
SET @qryDropEnum = 'DROP TYPE [dbo].[' + @oldEnumName + ']';
SET @qryDropEnumToList = 'DROP FUNCTION [dbo].[' + @oldEnumName + '.ToList]';
IF (EXISTS (SELECT 1 FROM Information_schema.Routines WHERE SPECIFIC_SCHEMA = 'dbo' AND SPECIFIC_NAME = @oldEnumName + '.ToList'))
EXEC sp_executesql @qryDropEnumToList;
EXEC sp_executesql @qryDropEnum;
FETCH NEXT FROM enumCursor INTO @oldEnumName;
END;
CLOSE enumCursor;
DEALLOCATE enumCursor;
-- dropping assemblies
DECLARE @qryDropAss NVARCHAR(MAX);
DECLARE @oldAssName NVARCHAR(MAX);
DECLARE assCursor CURSOR FOR SELECT Name FROM @asstodrop;
OPEN assCursor;
FETCH NEXT FROM assCursor INTO @oldAssName;
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT ' Dropping assembly ' + @oldAssName;
SET @qryDropAss = 'DROP ASSEMBLY [' + @oldAssName + ']'
EXEC sp_executesql @qryDropAss;
FETCH NEXT FROM assCursor INTO @oldAssName;
END;
CLOSE assCursor;
DEALLOCATE assCursor;
PRINT 'Current enums: clear.';
--============================================================================================================
-- creating new assembly
PRINT 'New assembly: generating...';
DECLARE @newAsmName NVARCHAR(255) = 'SQLAutoEnums.Generated.' + CAST(NEWID() AS NVARCHAR(100));
DECLARE @newAsmId BIGINT;
PRINT ' Generated assembly name = ' + @newAsmName;
DECLARE @code NVARCHAR(MAX) = dbo.SqlAutoEnumsGenerate('[SqlAutoEnums.Data.View]', 'Prefix', 'Name', 'MemberName', 'MemberValue');
DECLARE @compilemsg NVARCHAR(MAX) = dbo.SqlAutoEnumsTryCompile(@code);
DECLARE @bin VARBINARY(MAX) = dbo.SqlAutoEnumsCompile(@code);
IF (@bin IS NULL)
BEGIN
SET @msg = 'Cannot compile generated code:' + @compilemsg;
RAISERROR(@msg, 16, 2);
END;
DECLARE @qryCreateAssembly NVARCHAR(MAX) = 'CREATE ASSEMBLY [' + @newAsmName + '] FROM ' + master.dbo.fn_varbintohexstr(@bin) + ' WITH PERMISSION_SET = UNSAFE;';
EXEC sp_executesql @qryCreateAssembly;
SELECT @newAsmId = asm.assembly_id FROM sys.assemblies asm WHERE asm.name = @newAsmName;
PRINT 'New assembly: done.';
--===========================================================================================================
-- registering new enums
PRINT 'New enums: registering...'
DECLARE @qryCreateEnum NVARCHAR(MAX);
DECLARE @qryCreateEnumToList NVARCHAR(MAX);
DECLARE @newEnumName NVARCHAR(MAX);
DECLARE newEnumCursor CURSOR FOR
SELECT EnumName
FROM dbo.[SqlAutoEnums.Enums_New]() en
WHERE en.EnumName NOT IN (SELECT EnumName FROM dbo.[SqlAutoEnums.Enums_Current]());
OPEN newEnumCursor;
FETCH NEXT FROM newEnumCursor INTO @newEnumName;
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT ' Registering enum ' + @newEnumName;
SET @qryCreateEnum = 'CREATE TYPE [dbo].[' + @newEnumName + '] EXTERNAL NAME [' + @newAsmName + '].[SqlAutoEnumsGenerated.' + @newEnumName + ']';
EXEC sp_executesql @qryCreateEnum;
SET @qryCreateEnumToList = 'CREATE FUNCTION [' + @newEnumName + '.ToList]() RETURNS TABLE (ID INT, Name NVARCHAR(4000)) EXTERNAL NAME [' + @newAsmName + '].[SqlAutoEnumsGenerated.' + @newEnumName + '].ToList;';
EXEC sp_executesql @qryCreateEnumToList;
FETCH NEXT FROM newEnumCursor INTO @newEnumName;
END;
CLOSE newEnumCursor;
DEALLOCATE newEnumCursor;
PRINT 'New enums: done.'
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION;
THROW;
END CATCH;
END
GO
4. На таблицу вешаем триггер для автоматического обновления перечислений
CREATE TRIGGER dbo.[SqlAutoEnums.Renew.Trigger]...
CREATE TRIGGER dbo.[SqlAutoEnums.Renew.Trigger]
ON [dbo].[SqlAutoEnums.Data]
AFTER INSERT, DELETE, UPDATE
AS
BEGIN
EXEC dbo.[SqlAutoEnums.Renew];
END
Что теперь с этим делать?
Сгенерируем тестовые перечисления
INSERT INTO dbo.[SqlAutoEnums.Data]...
INSERT INTO dbo.[SqlAutoEnums.Data] (Prefix, Name, MemberName, MemberValue)
VALUES ('Enum', 'Lolly', 'C', 14 ),
('Enum', 'Lolly', 'A', 1 ),
('Enum', 'Lolly', 'B', 2 ),
('Enum', 'Process', 'Running', 1 ),
('Enum', 'Process', 'Suspended', 2 ),
('Enum', 'Process', 'Terminated', 3 )Посмотрим, что у нас теперь есть:
-- перечисления
SELECT * FROM [SqlAutoEnums.Enums_Current]()
-- перечисления с членами
SELECT * FROM [SqlAutoEnums.EnumsMembers_Current]()
-- члены перечисления EnumProcess
SELECT * FROM [EnumProcess.ToList]()
--используем переменные
DECLARE @processState EnumProcess;
SET @processState = EnumProcess::Running;
PRINT @processState.ToString();
--поля в таблицах
DECLARE @process TABLE (ID INT, Comment NVARCHAR(100), ProcessState EnumProcess);
INSERT INTO @process (ID, Comment, ProcessState)
VALUES (0, 'прямое присваивание: EnumProcess::Suspended', EnumProcess::Suspended),
(0, 'из строки: "Running" ', EnumProcess::Parse('Running')),
(0, 'из целого: 3', EnumProcess::Parse(3))
-- where
SELECT ID, Comment, ProcessState, ProcessState.ToInt(), ProcessState.ToString()
FROM @process
WHERE ProcessState = EnumProcess::Suspended OR
ProcessState = @processState;
-- group
SELECT ProcessState, ProcessState.ToInt(), ProcessState.ToString(), COUNT(*) AS [Count]
FROM @process
GROUP BY ProcessState;
Немного о внутренностях
Сборка SqlAutoEnums занимается, собственно, тем, что из данных в таблице генерирует и регистрирует сборки SqlAutoEnums.Generated.*, в которых и находятся перечисления. Ввиду ограничений CLR Hosted Enviroment (тынц, тынц), генерирует топорно, string.Format и поскакали, компилирует вызовом csc.exe по пути Environment.GetEnvironmentVariable(«windir») + "\Microsoft.NET\Framework\v3.5\csc.exe" (убедитесь, что на сервере установлен .NET 3.5 и есть доступ к Environment.GetEnvironmentVariable(«TEMP»)). Однажды заменим что-нибудь менее топорное.
Ввиду того, что SQLServer не знает и не понимает enum, формируются структуры с полями по имени членов перечислений:
public enum FooEnum{A = 1, B = 2}
public struct MyFooEnum
{
public MyFooEnum(FooEnum value) { _value = value; }
public static MyFooEnum A { get { return new MyFooEnum(FooEnum.A);} }
public static MyFooEnum B { get { return new MyFooEnum(FooEnum.B);} }
private FooEnum _value;
}
Вокруг наверчено еще много всякой технической обертки (подробнее о создании CLR типов можно почитать, например, здесь, MSDN, Google…
Следует помнить, что, как только вы использовали перечисление в работе (поля и вычисляемые поля в таблице, параметры процедур/функций и т.д.), то, как и любой другой пользовательский тип данных, изменить (ввиду отсутствия ALTER TYPE — делается DROP, затем CREATE) или удалить не получится, сначала надо убрать все ссылки на него. Впрочем, если тип используется только внутри кода процедур/функций, то можно, но, понятно, процедура/функция потом может вывалится с исключением, если не найдется перечисления или его части.
Поэтому при генерации новой сборки все перечисления, которые не имеют зависимостей, переносятся в нее, в «старой» сборке остаются перечисления, которые не нужно изменять и которые имеют зависимости.
Обещанные исходники сборки SqlAutoEnums.dll
Спасибо за внимание, надеюсь, кому-то пригодится вышеописанное или натолкнет на более продвинутый вариант.
Автор: yar229
