o create a CLR stored procedure in SQL Server….

I used this nice blog posting by Scott Sutherland as a starting point: https://blog.netspi.com/attacking-sql-server-clr-assemblies/

If you are using SQL Server 2017 it will be necessary to sign your CLR assembly.

If you are doing this in a development environment, you may wish to create a public/private key pair:
https://docs.microsoft.com/en-us/dotnet/framework/app-domains/how-to-create-a-public-private-key-pair

Once you’ve got your public key, you can reference it in your assembly at compile time.
https://docs.microsoft.com/en-us/dotnet/framework/app-domains/how-to-sign-an-assembly-with-a-strong-name

Note that not all assemblies are supported by sql server. A list of supported assemblies is available here:
https://docs.microsoft.com/en-us/sql/relational-databases/clr-integration/database-objects/supported-net-framework-libraries?view=sql-server-2017

Here is a stackoverflow article that talks about registering the assemblies necessary to use JSON serialization.
https://stackoverflow.com/questions/34772921/sqlclr-function-and-system-runtime-serialization-in-gac

Here’s the sample dot net CLR assembly from Scott’s blog posting.

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.IO;
using System.Diagnostics;
using System.Text;
using System.Reflection;

[assembly: AssemblyKeyFileAttribute(@"F:\research\publicprivatekey\public.snk")]
[assembly: AssemblyDelaySignAttribute(true)]

//don't use a namespace

public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void cmd_exec(SqlString execCommand)
    {
        Process proc = new Process();
        proc.StartInfo.FileName = @"C:\Windows\System32\cmd.exe";
        proc.StartInfo.Arguments = string.Format(@" /C {0}", execCommand.Value);
        proc.StartInfo.UseShellExecute = false;
        proc.StartInfo.RedirectStandardOutput = true;
        proc.Start();

        // Create the record and specify the metadata for the columns.
        SqlDataRecord record = new SqlDataRecord(new SqlMetaData("output", SqlDbType.NVarChar, 4000));

        // Mark the beginning of the result set.
        SqlContext.Pipe.SendResultsStart(record);

        // Set values for each column in the row
        record.SetString(0, proc.StandardOutput.ReadToEnd().ToString());

        // Send the row back to the client.
        SqlContext.Pipe.SendResultsRow(record);

        // Mark the end of the result set.
        SqlContext.Pipe.SendResultsEnd();

        proc.WaitForExit();
        proc.Close();
    }
}

Once you’ve compiled the assembly, you need to put it in a place where the sql server service principal has permissions.

I created a directory, here:

C:\Program Files (x86)\Microsoft SQL Server\CLRSP

You’ll then need to run some sql commands to turn on CLR procedures (if necessary) and to register the new CLR stored procedure. Note that the text in brackets (“sqlclrtest.sqlclrtest”) includes the namespace.

use msdb

-- Enable show advanced options on the server
sp_configure 'show advanced options',1
RECONFIGURE
GO
-- Enable clr on the server
sp_configure 'clr enabled',1
RECONFIGURE
GO
--drop assembly sqlclrtest
-- Import the assembly
CREATE ASSEMBLY [sqlclrtest.sqlclrtest]
FROM 'C:\Program Files (x86)\Microsoft SQL Server\CLRSP\sqlclrtest.dll'
WITH PERMISSION_SET = UNSAFE;

-- Link the assembly to a stored procedure
CREATE PROCEDURE [dbo].[sqlclrtest] @execCommand NVARCHAR (4000) AS EXTERNAL NAME [sqlclrtest.sqlclrtest].[StoredProcedures].[cmd_exec];
GO

In this example, the new CLR stored procedure can then be used to run a sql command.

[sqlclrtest] 'dir'

 Volume in drive C has no label.   Volume Serial Number is AA1D-9186     Directory of C:\WINDOWS\system32    08/27/2018  09:32 AM    
          .  08/27/2018  09:32 AM    
          ..  08/03/2018  04:27 PM    
          ....