SHARE
TWEET

Untitled

a guest Sep 15th, 2015 147 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. USE AdventureWorks2012;
  2. GO
  3.  
  4. -- Enable CLR
  5. EXEC sp_configure 'clr enabled', 1;
  6. GO
  7. RECONFIGURE;
  8. GO
  9.  
  10. -- Create the assembly based on the dll generated by compiling the CLR project
  11. -- I've also included the "assembly bits" version that can be run without compiling
  12. CREATE ASSEMBLY ClrPlayground
  13. --FROM 'C:\FullPathGoesHere\ClrPlayground\bin\Debug\ClrPlayground.dll'
  14. FROM 0x4d5a90000300000004000000ffff0000b800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000e1fba0e00b409cd21b8014ccd21546869732070726f6772616d2063616e6e6f742062652072756e20696e20444f53206d6f64652e0d0d0a2400000000000000504500004c0103008233f3550000000000000000e00002210b010b000016000000060000000000004e340000002000000040000000000010002000000002000004000000000000000600000000000000008000000002000000000000030060850000100000100000000010000010000000000000100000000000000000000000003400004b00000000400000b802000000000000000000000000000000000000006000000c000000c83200001c0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002e7465787400000054140000002000000016000000020000000000000000000000000000200000602e72737263000000b8020000004000000004000000180000000000000000000000000000400000402e72656c6f6300000c0000000060000000020000001c000000000000000000000000000040000042000000000000000000000000000000003034000000000000480000000200050020240000a80e000001000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001b3002008500000001000011007201000070730600000a0a00066f0700000a00066f0800000a0c000872330000706f0900000a00086f0a00000a0d00090f00280b00000a28020000060b00de120914fe01130511052d07096f0c00000a00dc0000de120814fe01130511052d07086f0c00000a00dc00071304de120614fe01130511052d07066f0c00000a00dc0011042a0000000128000002002f00124100120000000002001b003c5700120000000002000c00636f00120000000013300400390100000200001100160a160b730d00000a0c021428030000060d38090100000009130402110428030000060d0811046f0e00000a00092c2e092c2711046f06000006096f06000006331411046f08000006096f08000006280f00000a2b0117002b0116002b0117001305110516fe01130911093a8a000000002b06000617580a0006086f1000000a2f2708066f1100000a6f0800000611046f08000006130a120a15035a6c281200000a281300000a2b011600130911092dc208066f1100000a6f0e000006130611046f0c00000611046f0e0000065811065913072b150008072517580b6f1100000a11076f11000006000007086f1000000afe04130911092ddc00092c1111046f06000006096f06000006fe012b011700130911092d0900086f1000000a0a00000914fe0116fe01130911093ae7feffff0813082b0011082a00000013300300a30000000300001100140a026f1400000a16fe010d093a8a0000000073120000060b0702166f1500000a6f07000006000702176f1600000a6f09000006000702186f1500000a6f0b000006000702196f1700000a6f0d00000600072300000000000000006f0f00000600070a0314fe010d092d300006066f06000006036f060000062e0b2300000000000000002b0d036f0e000006036f0c00000658006f0f000006000000060c2b00082a00133002004000000004000011000274030000020a03066f060000065404066f08000006810600000105066f0a000006540e04066f0c000006570e05066f0e000006570e06066f10000006572a1e02281800000a2a133001000b00000005000011027b010000040a2b00062a2202037d010000042a133001000b00000006000011027b020000040a2b00062a2202037d020000042a133001000b00000005000011027b030000040a2b00062a2202037d030000042a133001000b00000007000011027b040000040a2b00062a2202037d040000042a133001000b00000007000011027b050000040a2b00062a2202037d050000042a133001000b00000007000011027b060000040a2b00062a2202037d060000042a1e02281800000a2a42534a4201000100000000000c00000076342e302e33303331390000000005006c00000068040000237e0000d40400009405000023537472696e677300000000680a0000c001000023555300280c0000100000002347554944000000380c00007002000023426c6f6200000000000000020000015715a2090902000000fa25330016000001000000130000000300000006000000120000001200000019000000160000000700000001000000060000000c0000000100000001000000020000000100000000000a0001000000000006005000490006006a0057000a00970082000600c900ae000a00e600d00006001301490006004f0330030600be03ab032300d203000006000104e10306002104e1030a0068044d040a007d04d0000a009e048b040a00b004d0000a00c9048b040600fb0449000a0048058b0406007905e1030000000001000000000001000100010010001c00000005000100010003001000310000000500010006000100f401590001000f025c0001003002590001005202600001006e0260000100870260005020000000009600a0000a0001000c21000000009100f40011000200542200000000910007011d00040004230000000096001c012600060050230000000086182c0138000d00582300000000860832013c000d006f23000000008608400140000d0078230000000086084e0145000e008f2300000000860862014a000e00982300000000860876013c000f00af230000000086088b0140000f00b823000000008608a00150001000cf23000000008608af0154001000d823000000008608be0150001100ef23000000008608ca0154001100f823000000008608d601500012000f24000000008608e5015400120018240000000086182c013800130000000100ec0200000100fe0200000200ec02000001000503000002000703000001001003020002002603020003005c03020004006c03020005007d03020006008803020007009a0300000100a50300000100a50300000100a50300000100a50300000100a50300000100a50339002c01380041002c01700051002c01400059002c01380061002c01380069002c01ad017100ab0438006900bb04b2018100d304ad017900e304b7011900f1043c008900070538000c002c0138000c000f05d50131001305db010c0021053c000c002b05e30131003405e90131003c05db01910055050a0291005a050e0291006305130291006f05190209002c01380099002c01380020002b0076002100cb002d022e0013003f022e00230051022e001b0048024100cb002d026100cb002d028100cb002d02a100cb002d02c000cb002d02c100cb002d02e000cb002d020001cb002d022001cb002d024001cb002d026001cb002d028001cb002d02a001cb002d02c001cb002d02e001cb002d020002cb002d022002cb002d02bc01ef011e022802320236023b02030001000000a30263000000ad0267000000bd0263000000ce026c000000d9026c000000e1026c0002000600030001000700030002000800050001000900050001000b00070002000a00070002000c00090001000d00090002000e000b0001000f000b00020010000d00010011000d00ce010480000000000000000000000000000000003f04000004000000000000000000000001004000000000000400000000000000000000000100760000000000030002000000003c4d6f64756c653e00436c72506c617967726f756e642e646c6c0055736572446566696e656446756e6374696f6e73005472786e526f6c6c696e6753756d006d73636f726c69620053797374656d004f626a6563740053797374656d2e436f6c6c656374696f6e730049456e756d657261626c650053797374656d2e446174610053797374656d2e446174612e53716c54797065730053716c496e74333200526f6c6c696e6753756d5476660053797374656d2e436f6c6c656374696f6e732e47656e65726963004c69737460310053797374656d2e446174612e53716c436c69656e740053716c4461746152656164657200436f6d70757465526f6c6c696e6753756d73004765744e6578745472786e004461746554696d6500526f6c6c696e6753756d5f46696c6c002e63746f72006765745f50726f647563744964007365745f50726f647563744964006765745f5472616e73616374696f6e44617465007365745f5472616e73616374696f6e44617465006765745f5265666572656e63654f726465724964007365745f5265666572656e63654f726465724964006765745f41637475616c436f7374007365745f41637475616c436f7374006765745f5072657653756d007365745f5072657653756d006765745f526f6c6c696e6753756d007365745f526f6c6c696e6753756d003c50726f6475637449643e6b5f5f4261636b696e674669656c64003c5472616e73616374696f6e446174653e6b5f5f4261636b696e674669656c64003c5265666572656e63654f7264657249643e6b5f5f4261636b696e674669656c64003c41637475616c436f73743e6b5f5f4261636b696e674669656c64003c5072657653756d3e6b5f5f4261636b696e674669656c64003c526f6c6c696e6753756d3e6b5f5f4261636b696e674669656c640050726f647563744964005472616e73616374696f6e44617465005265666572656e63654f7264657249640041637475616c436f7374005072657653756d00526f6c6c696e6753756d00726f6c6c696e67506572696f644461797300726561646572007200637572725472786e007472786e57697468526f6c6c696e6753756d4f626a0070726f6475637449640053797374656d2e52756e74696d652e496e7465726f705365727669636573004f7574417474726962757465007472616e73616374696f6e44617465007265666572656e63654f7264657249640061637475616c436f7374007072657643756d756c617469766553756d00726f6c6c696e6753756d0076616c75650053797374656d2e446961676e6f73746963730044656275676761626c6541747472696275746500446562756767696e674d6f6465730053797374656d2e52756e74696d652e436f6d70696c6572536572766963657300436f6d70696c6174696f6e52656c61786174696f6e734174747269627574650052756e74696d65436f6d7061746962696c69747941747472696275746500436c72506c617967726f756e64004d6963726f736f66742e53716c5365727665722e5365727665720053716c46756e6374696f6e4174747269627574650053716c436f6e6e656374696f6e0053797374656d2e446174612e436f6d6d6f6e004462436f6e6e656374696f6e004f70656e0053716c436f6d6d616e6400437265617465436f6d6d616e64004462436f6d6d616e64007365745f436f6d6d616e64546578740045786563757465526561646572006765745f56616c75650049446973706f7361626c6500446973706f736500416464006f705f496e657175616c697479006765745f436f756e74006765745f4974656d0041646444617973006f705f4c6573735468616e00446244617461526561646572005265616400476574496e743332004765744461746554696d6500476574446f75626c6500436f6d70696c657247656e65726174656441747472696275746500003163006f006e007400650078007400200063006f006e006e0065006300740069006f006e003d0074007200750065003b00008189530045004c004500430054002000500072006f006400750063007400490064002c0020005400720061006e00730061006300740069006f006e0044006100740065002c0020005200650066006500720065006e00630065004f007200640065007200490044002c000d000a00090009000900090009000900090009000900090043004100530054002800410063007400750061006c0043006f0073007400200041005300200046004c004f004100540029002000410053002000410063007400750061006c0043006f007300740020000d000a00090009000900090009000900090009000900460052004f004d002000500072006f00640075006300740069006f006e002e005400720061006e00730061006300740069006f006e0048006900730074006f007200790020000d000a000900090009000900090009000900090009004f0052004400450052002000420059002000500072006f006400750063007400490064002c0020005400720061006e00730061006300740069006f006e004400610074006500000000afeebcc7b6ff6a4a967104b63f2a7ba00008b77a5c561934e0890600011209110d0b000215121101120c121508080002120c1215120c110007011c10081011191008100d100d100d0320000103200008042001010804200011190520010111190320000d042001010d0206080306111902060d0328000804280011190328000d0520010111258135010003005455794d6963726f736f66742e53716c5365727665722e5365727665722e446174614163636573734b696e642c2053797374656d2e446174612c2056657273696f6e3d342e302e302e302c2043756c747572653d6e65757472616c2c205075626c69634b6579546f6b656e3d623737613563353631393334653038390a4461746141636365737301000000540e1146696c6c526f774d6574686f644e616d650f526f6c6c696e6753756d5f46696c6c540e0f5461626c65446566696e6974696f6e6f50726f64756374496420494e542c205472616e73616374696f6e44617465204441544554494d452c205265666572656e63654f72646572494420494e542c41637475616c436f737420464c4f41542c205072657653756d20464c4f41542c20526f6c6c696e6753756d20464c4f4154042001010e042000123d0420001215110706123515121101120c123d12151209020615121101120c052001011300070002021119111905200113000805200111190d1a070b080815121101120c120c120c020d0d15121101120c0211190320000204200108080520011119080420010d08090704120c120c120c02040701120c04010000000307010804070111190307010d0801000701000000000801000800000000001e01000100540216577261704e6f6e457863657074696f6e5468726f777301000000008233f35500000000020000001c010000e4320000e414000052534453d1e85ad984319c408abd064f4389ee3741000000633a5c6465765c6170745c5363616c6162696c6974795c434c525c436c72506c617967726f756e645c436c72506c617967726f756e645c6f626a5c44656275675c436c72506c617967726f756e642e706462000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000002834000000000000000000003e340000002000000000000000000000000000000000000000000000303400000000000000005f436f72446c6c4d61696e006d73636f7265652e646c6c0000000000ff25002000100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001001000000018000080000000000000000000000000000001000100000030000080000000000000000000000000000001000000000048000000584000005c02000000000000000000005c0234000000560053005f00560045005200530049004f004e005f0049004e0046004f0000000000bd04effe00000100000000000000000000000000000000003f000000000000000400000002000000000000000000000000000000440000000100560061007200460069006c00650049006e0066006f00000000002400040000005400720061006e0073006c006100740069006f006e00000000000000b004bc010000010053007400720069006e006700460069006c00650049006e0066006f0000009801000001003000300030003000300034006200300000002c0002000100460069006c0065004400650073006300720069007000740069006f006e000000000020000000300008000100460069006c006500560065007200730069006f006e000000000030002e0030002e0030002e003000000044001200010049006e007400650072006e0061006c004e0061006d006500000043006c00720050006c0061007900670072006f0075006e0064002e0064006c006c0000002800020001004c006500670061006c0043006f0070007900720069006700680074000000200000004c00120001004f0072006900670069006e0061006c00460069006c0065006e0061006d006500000043006c00720050006c0061007900670072006f0075006e0064002e0064006c006c000000340008000100500072006f006400750063007400560065007200730069006f006e00000030002e0030002e0030002e003000000038000800010041007300730065006d0062006c0079002000560065007200730069006f006e00000030002e0030002e0030002e00300000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000003000000c000000503400000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
  15. WITH PERMISSION_SET = safe;
  16. GO
  17.  
  18. --Create a function from the assembly
  19. CREATE FUNCTION dbo.RollingSumTvf (@rollingPeriodDays INT)
  20. RETURNS TABLE ( ProductId INT, TransactionDate DATETIME, ReferenceOrderID INT,
  21.                 ActualCost FLOAT, PrevCumulativeSum FLOAT, RollingSum FLOAT)
  22. -- The function yields rows in order, so let SQL Server know to avoid an extra sort
  23. ORDER (ProductID, TransactionDate, ReferenceOrderID)
  24. AS EXTERNAL NAME ClrPlayground.UserDefinedFunctions.RollingSumTvf;
  25. GO
  26.  
  27. -- Now we can actually use the TVF!
  28. SELECT *
  29. FROM dbo.RollingSumTvf(45)
  30. ORDER BY ProductId, TransactionDate, ReferenceOrderId
  31. GO
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
Top