GROUP_CONCAT aggregate function in sql server

Sql server has not inbuilt GROUP_CONCAT function to get aggregate data in concatenated format just like MYSQL. Sql server has power of CLR objects we can create this aggregate function.  

I have already created user defined GROUP_CONCAT function in sql server. To use this you have to only execute this script one time:

EXECUTE SP_CONFIGURE 'clr enabled', 1

GO

RECONFIGURE

GO

ALTER DATABASE <YourDatabaseName> SET TRUSTWORTHY ON

GO

ALTER AUTHORIZATION ON DATABASE:: <YourDatabaseName> TO sa

GO

CREATE ASSEMBLY [CLRAggrigates]

AUTHORIZATION [dbo]

FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C0104009B7B5B530000000000000000E00002210B010800000C0000000E0000000000002E2A000000200000004000000000400000200000000200000400000000000000040000000000000000A000000004000000000000020040850000100000100000000010000010000000000000100000000000000000000000E02900004B000000006000005008000000000000000000000000000000000000008000000C000000004000001C0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E74657874000000340A000000200000000C000000040000000000000000000000000000200000602E7364617461000084000000004000000002000000100000000000000000000000000000400000C02E727372630000005008000000600000000A000000120000000000000000000000000000400000402E72656C6F6300000C0000000080000000020000001C0000000000000000000000000000400000420000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000102A000000000000480000000200050028210000B808000001000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000002602280300000A00002A00004E00027E0400000A280500000A7D01000004002A13300300420000000000000000027B010000047E0400000A280500000A280600000A280700000A2C0902037D010000042B190002027B0100000404280800000A03280800000A7D0100000400002A00006A0002036F050000067E0400000A280500000A6F0300000600002A00133001000C0000000100001100027B010000040A2B00062A520002036F0900000A280500000A7D01000004002A0000006E0003027C01000004FE16030000016F0A00000A6F0B00000A00002A42534A4201000100000000000C00000076322E302E35303732370000000005006C00000004030000237E0000700300009003000023537472696E6773000000000007000008000000235553000807000010000000234755494400000018070000A001000023426C6F620000000000000002000001571702020900000000FA253300160000010000001900000002000000010000000700000006000000010000001B000000110000000100000002000000010000000300000000007D0301000000000006004B0044000E0079005E000E00A50090000600F600EC0006000A01EC0006001F0144000E00380190000E0077015E000E0098015E0006009F0144000E00B5015E000600DA01C7010600F701C70137000B02000006003A021A0206005A021A020600970278020600A50278020600B90244000600E102CF020600FC02CF0206001703CF0206003003CF0206004903CF0206006603CF02000000000100000000000100010001200000290036000500010001000100AF00170050200000000006188A00130001005C20000000000600BC00130001007020000000000600C1001B000100C020000000000600DC0023000300DC20000000000600E20029000400F42000000000660303012E0005000C2100000000660317013400060000000100CC0000000200D20000000100CC00000000000000000001000801000001001D0102000900110003012E0011001701340009008A001300310026013A0019002C013D0019004301430039004F014C0019005701520021006301600009006E01600029001701640041008A00690051008A00130059008A00130061008A00130069008A00FC0079008A00020181008A00130089008A00640091008A00070199008A000701A1008A006400A9008A006400B1008A006400B9008A006400C1008A006400C9008A00640020007B00F70024007300D1002E009B003D012E00A30067012E00AB006D012E00DB008B012E008B0015012E0093001E012E00B30067012E00BB0073012E00D30067012E0083000C012E00C3008B012E00CB006701430063006F0044007300E40084007300D1005B0002000C00030002000E00050004800000010000006D147E4D0000000000003600000002000000000000000000000001000A00000000000800000000000000000000000A00130000000000020000000000000000000000010052000000000000000000003C4D6F64756C653E006D73636F726C6962004D6963726F736F66742E56697375616C42617369630047524F55505F434F4E43415400434C52416767726967617465730053797374656D004F626A6563740053797374656D2E44617461004D6963726F736F66742E53716C5365727665722E536572766572004942696E61727953657269616C697A65002E63746F720053797374656D2E446174612E53716C54797065730053716C537472696E670053747241676772696761746500496E697400416363756D756C6174650076616C756500536570617261746F72004D65726765005465726D696E6174650053797374656D2E494F0042696E617279526561646572005265616400720042696E617279577269746572005772697465007700537472696E6700456D707479006F705F496D706C696369740053716C426F6F6C65616E006F705F457175616C697479006F705F54727565006F705F4164646974696F6E0052656164537472696E6700546F537472696E670053716C55736572446566696E656441676772656761746541747472696275746500466F726D61740053657269616C697A61626C654174747269627574650053716C46616365744174747269627574650053797374656D2E446961676E6F73746963730044656275676765724E6F6E55736572436F64654174747269627574650044656275676761626C6541747472696275746500446562756767696E674D6F6465730053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C6974794174747269627574650053797374656D2E52756E74696D652E496E7465726F705365727669636573004775696441747472696275746500436F6D56697369626C6541747472696275746500434C53436F6D706C69616E744174747269627574650053797374656D2E5265666C656374696F6E00417373656D626C7954726164656D61726B41747472696275746500417373656D626C79436F7079726967687441747472696275746500417373656D626C7950726F6475637441747472696275746500417373656D626C79436F6D70616E7941747472696275746500417373656D626C794465736372697074696F6E41747472696275746500417373656D626C795469746C6541747472696275746500434C52416767726967617465732E646C6C000000032000000000008DD472E8019E5B428A80BB565483CA350008B77A5C561934E08908B03F5F7F11D50A3A032000010306110D07200201110D110D052001011208042000110D05200101121105200101121502060E050001110D0E080002111D110D110D05000102111D080002110D110D110D040701110D0320000E042001010E0520010111256101000200000004005402124973496E76617269616E74546F4E756C6C73015402174973496E76617269616E74546F4475706C696361746573005402124973496E76617269616E74546F4F726465720054080B4D61784279746553697A65FFFFFFFF12010001005408074D617853697A65FFFFFFFF12010001005408074D617853697A65320000000401000000052001011139042001010804200101020801000301000000000801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F7773012901002439383136656564642D613461642D346137312D383332362D343532393063303939376635000005010000000005010001000017010012436F7079726967687420C2A920203230313400001201000D434C524167677269676174657300000000082A000000000000000000001E2A0000002000000000000000000000000000000000000000000000102A00000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF250020400000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000009B7B5B530000000002000000680000001C4000001C100000525344534D70576EC35D384D9BF3F7F5BCBCC6F101000000463A5C50726F6A656374735C50726F6A656374323031345C434C52416767726967617465735C434C52416767726967617465735C6F626A5C44656275675C434C52416767726967617465732E7064620000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000030003000000280000800E000000480000801000000060000080000000000000000000000000000002000200000078000080030000009000008000000000000000000000000000000100007F0000A80000800000000000000000000000000000010001000000C00000800000000000000000000000000000010000000000D80000000000000000000000000000000000010000000000E80000000000000000000000000000000000010000000000F800000000000000000000000000000000000100000000000801000018640000E80200000000000000000000006700002801000000000000000000002868000022000000000000000000000018610000000300000000000000000000000334000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE00000100000001007E4D6D14000001007E4D6D143F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B00460020000010053007400720069006E006700460069006C00650049006E0066006F0000003C020000010030003000300030003000340062003000000044000E000100460069006C0065004400650073006300720069007000740069006F006E000000000043004C0052004100670067007200690067006100740065007300000040000F000100460069006C006500560065007200730069006F006E000000000031002E0030002E0035003200320039002E00310039003800330038000000000044001200010049006E007400650072006E0061006C004E0061006D006500000043004C00520041006700670072006900670061007400650073002E0064006C006C0000004800120001004C006500670061006C0043006F007000790072006900670068007400000043006F0070007900720069006700680074002000A90020002000320030003100340000004C00120001004F0072006900670069006E0061006C00460069006C0065006E0061006D006500000043004C00520041006700670072006900670061007400650073002E0064006C006C0000003C000E000100500072006F0064007500630074004E0061006D0065000000000043004C0052004100670067007200690067006100740065007300000044000F000100500072006F006400750063007400560065007200730069006F006E00000031002E0030002E0035003200320039002E00310039003800330038000000000048000F00010041007300730065006D0062006C0079002000560065007200730069006F006E00000031002E0030002E0035003200320039002E003100390038003300380000000000280000002000000040000000010004000000000080020000000000000000000000000000000000000000000000008000008000000080800080000000800080008080000080808000C0C0C0000000FF0000FF000000FFFF00FF000000FF00FF00FFFF0000FFFFFF0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000007777777777777777777777777777700444444444444444444444444444447004FFFFFFFFFFFFFFFFFFFFFFFFFFF47004FFFFFFFFFFFFFFFFFFFFFFFFFFF47004FFFFFFFFFFFFFFFFFFFFFFFFFFF47004FFFFFFFFFFFFFFFFFFFFFFFFFFF47004FFFFFFFFFFFFFFFFFFFFFFFFFFF47004FFFFFFFFFFFFFFFFFFFFFFFFFFF47004FFFFFFFFFFFFFFFFFFFFFFFFFFF47004FFFFFFFFFFFFFFFFFFFFFFFFFFF47004FFFFFFFFFFFFFFFFFFFFFFFFFFF47004FFFFFFFFFFFFFFFFFFFFFFFFFFF47004FFFFFFFFFFFFFFFFFFFFFFFFFFF47004FFFFFFFFFFFFFFFFFFFFFFFFFFF47004FFFFFFFFFFFFFFFFFFFFFFFFFFF47004FFFFFFFFFFFFFFFFFFFFFFFFFFF47004FFFFFFFFFFFFFFFFFFFFFFFFFFF47004FFFFFFFFFFFFFFFFFFFFFFFFFFF47004FFFFFFFFFFFFFFFFFFFFFFFFFFF47004FFFFFFFFFFFFFFFFFFFFFFFFFFF4700488888888888888888888888888847004444444444444444444444444444470044C4C4C4C4C4C4C4C4C4ECECE49747004CCCCCCCCCCCCCCCCCCCCCCCCCCC40000444444444444444444444444444000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFC00000018000000180000001800000018000000180000001800000018000000180000001800000018000000180000001800000018000000180000001800000018000000180000001800000018000000180000001800000018000000180000003C0000007FFFFFFFFFFFFFFFFFFFFFFFF2800000010000000200000000100040000000000C0000000000000000000000000000000000000000000000000008000008000000080800080000000800080008080000080808000C0C0C0000000FF0000FF000000FFFF00FF000000FF00FF00FFFF0000FFFFFF000000000000000000077777777777777744444444444444474FFFFFFFFFFFF8474FFFFFFFFFFFF8474FFFFFFFFFFFF8474FFFFFFFFFFFF8474FFFFFFFFFFFF8474FFFFFFFFFFFF8474FFFFFFFFFFFF8474FFFFFFFFFFFF84748888888888888474CCCCCCCCCCCCC47C4444444444444C000000000000000000000000000000000FFFF000080000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000010000FFFF0000FFFF00000000010002002020100001000400E802000002001010100001000400280100000300000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000002000000C000000303A00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000

WITH PERMISSION_SET = EXTERNAL_ACCESS

GO

CREATE AGGREGATE [dbo].[GROUP_CONCAT](

@value [nvarchar](max),

@Separator [nvarchar](50))

RETURNS[nvarchar](max)

EXTERNAL NAME [CLRAggrigates].[CLRAggrigates.GROUP_CONCAT]

GO

Note: In the above script replace <YourDatabaseName> by your database name.

You must have sufficient permission execute above script. If you face any problems please put your note in the comment section.

Now you can use group_conact aggregate function. For examples:

SELECT

ntUserID,

    dbo.GROUP_CONCAT([ntQuantity],',') AS vcQuantity

 FROM tblOrder

 GROUP BY ntUserID

Sample output:

ntUserID	vcQuantity
1	20,15,15
2	20,15,15
3	20,15,15
4	20,15,15

How to transfer or create CLR stored procedure from one database to other database in sql server

We can transfer or move or create CLR stored procedures from one database or from one sql server to other sql server (Other location):

Step 1: In the destination database where you want to create CLR stored procedure executes following sql queries:

sp_configure 'clr enabled', 1

GO

RECONFIGURE

GO

ALTER DATABASE DestinationDB SET TRUSTWORTHY ON

EXEC dbo.sp_changedbowner @loginame = N'sa', @map = false

Note: Replace the DestinationDB by your database name.

Step 2: Right click on assembly of your stored procedure in the source database and choose Script Assembly As -> Create To -> New Query Editor Window

Step 3: You will get create assembly script something like this:

CREATE ASSEMBLY [DemoCLR]

AUTHORIZATION [dbo]

FROM 0x4D5A90000300000004...

WITH PERMISSION_SET = UNSAFE

GO

ALTER ASSEMBLY [DemoCLR]

ADD FILE FROM 0x4D6963726F

AS N'bin\Debug\DemoCLR.pdb'

GO

ALTER ASSEMBLY [DemoCLR]

ADD FILE FROM 0xEFBBBF496D706F727...

AS N'My Project\AssemblyInfo.vb'

GO

ALTER ASSEMBLY [DemoCLR]

ADD FILE FROM 0xEFBBBF496D706F72...

AS N'uspGetEmployeeInfo.vb'

GO

EXEC sys.sp_addextendedproperty @name=N'SqlAssemblyProjectRoot', @value=N'F:\Projects\DemoCLR\DemoCLR' , @level0type=N'ASSEMBLY',@level0name=N'DemoCLR'

GO

From above script find out path of .dll file of CLR stored procedure. In above script it shown in bold characters. In this example it is:   F:\Projects\DemoCLR\DemoCLR . Copy the .dll  file (In this example DemoCLR.dll)  from that path and copy it destination sql server where you want to create clr procedure. Edit the path in above script according to path of dll file in destination sql server in above script and execute the above script to create assembly.

Step 4: In the same way in SSMS generate the create stored procedure script of your CLR stored procedure. It will look something this:

CREATE PROCEDURE [dbo].[uspGetEmployeeInfo]

    @StatusMsg [nvarchar](4000) OUTPUT

WITH EXECUTE AS CALLER

AS

EXTERNAL NAME [DemoCLR].[DemoCLR.StoredProcedures].[uspGetEmployeeInfo]

GO

EXEC sys.sp_addextendedproperty @name=N'SqlAssemblyFile', @value=N'uspGetEmployeeInfo.vb' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'PROCEDURE',@level1name=N'uspGetEmployeeInfo'

GO

EXEC sys.sp_addextendedproperty @name=N'SqlAssemblyFileLine', @value=N'14' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'PROCEDURE',@level1name=N'uspGetEmployeeInfo'

GO

Execute the above script in the destination database. 

How to create CLR stored procedure in visual studio 2010 in vb.net.
Output parameters of CLR stored procedure in vb.net. 

Output parameters of CLR stored procedure in vb.net

Vb.net code for output parameters of CLR stored procedure

Output parameter of CLR procedure must be passed by reference. Output parameters are must have attribute <Out ()>. For this we have to import System.Runtime.InteropServices

Imports System

Imports System.Data

Imports System.Data.SqlClient

Imports System.Data.SqlTypes

Imports Microsoft.SqlServer.Server

Imports System.Runtime.InteropServices

Partial Public Class StoredProcedures

    Private Shared command As SqlCommand

    Private Shared reader As SqlDataReader

    <Microsoft.SqlServer.Server.SqlProcedure()> _

    Public Shared Sub uspGetEmployeeInfo(<Out()> ByRef StatusMsg As String)

        Try

            Using connection As New SqlConnection("context connection=true")

                connection.Open()

                command = New SqlCommand("SELECT EmpName,Age,Country FROM Employee", connection)

                reader = command.ExecuteReader()

                SqlContext.Pipe.Send(reader)

            End Using

            StatusMsg = "Success"

        Catch ex As Exception

            StatusMsg = ex.Message

        End Try

    End Sub

End Class

Sql query to execute:

DECLARE  @StatusMsg NVARCHAR(4000)

EXEC [dbo].[uspGetEmployeeInfo] @StatusMsg OUTPUT

SELECT   @StatusMsg AS StatusMsg

How to create CLR stored procedure in visual studio 2010 in vb.net
How to transfer or create CLR stored procedure from one database to other database in sql server.

How to create CLR stored procedure in visual studio 2010 in vb.net

We can create CLR stored procedure in visual studio 2010 in vb.net very easily if you following steps:

Step 1: Create a Visual Basic SQL CLR Database Project in visual studio.

Step 2: You will get Add Database Reference window. Click on Add New Reference...

Step 3: In New Database Reference window fill database information where you want to create CLR stored procedure.

Step 4: In pop up, you should enable SQL/CLR debugging for this connection.

Step 5: Right-click on your project in solution explorer and choose Add -> New Item...

Now select Stored Procedure and write down any name of your stored procedure.

Step 6: You will get auto gendered code something like this:

Imports System

Imports System.Data

Imports System.Data.SqlClient

Imports System.Data.SqlTypes

Imports Microsoft.SqlServer.Server

Partial Public Class StoredProcedures

    <Microsoft.SqlServer.Server.SqlProcedure()> _

    Public Shared Sub  uspGetEmployeeInfo ()

        ' Add your code here

    End Sub

End Class

Step 7: Here you can write code of your stored procedure according to your requirement. In this example, we are going to create such CLR stored procedure which displays the information of employees from Employee table. Open SQL server management studio (SSMS):

a. Creating Employee table in the SampleDb database.

USE [SampleDB]

CREATE TABLE [dbo].[Employee](

    [EmpID] BIGINT IDENTITY(1,1) NOT NULL PRIMARY KEY,

    [EmpName] VARCHAR(100) NULL,

    [Age] TINYINT NOT NULL DEFAULT(0),

    [Country] VARCHAR(50) NULL,

 )

b. Insert some data into it. Let us assume Employee table has following data:

EmpID	EmpName	Age	Country
1	Scott	32	USA
2	Greg	31	UK
3	Akram	30	Pakistan
4	Rohit	23	India
5	Nicolus	28	Russia

c. Enable "clr enabled" configuration option.

EXECUTE SP_CONFIGURE 'clr enabled', 1

GO

RECONFIGURE

GO

d. Set your database trustworthy:

ALTER DATABASE SampleDB SET TRUSTWORTHY ON

Step 8: Edit the code of CLR stored procedure in visual studio:

Imports System

Imports System.Data

Imports System.Data.SqlClient

Imports System.Data.SqlTypes

Imports Microsoft.SqlServer.Server

Partial Public Class StoredProcedures

    Private Shared command As SqlCommand

    Private Shared reader As SqlDataReader

    <Microsoft.SqlServer.Server.SqlProcedure()> _

    Public Shared Sub uspGetEmployeeInfo()

        Using connection As New SqlConnection("context connection=true")

            connection.Open()

            command = New SqlCommand("SELECT EmpName,Age,Country FROM Employee", connection)

            reader = command.ExecuteReader()

            SqlContext.Pipe.Send(reader)

        End Using

    End Sub

End Class

Step 9: It is not possible to create CLR stored procedure in .net framework 4.0 so change it to 3.5

For this right click on your project and choose Properties. Now select Compile tab and click on Advanced Compile options... and change the .net framework

Step 10: Now make your project unsafe. For this select Database tab in the properties window of your project and choose Permission level Unsafe.

Step 11: Now build your solution and deployed your project.

Step 12: If deployment is successful then it will create CLR stored procedure in your database in SSMS.

Step 13: We cannot modify or see the script of CLR procedure in SSMS. Here we can only execute this stored procedure

USE [SampleDB]

DECLARE  @return_value INT

EXEC @return_value = [dbo].[uspGetEmployeeInfo]

SELECT   'Return Value' = @return_value

Output:

EmpName	Age	Country
Scott	32	USA
Greg	31	UK
Akram	30	Pakistan
Rohit	23	India
Nicolus	28	Russia

Note: It will also create one assembly inside Programmability -> Assemblies of SSMS

Output parameters of CLR stored procedure in vb.net.
How to transfer or create CLR stored procedure from one database to another database in SQL server.