title: SQL Server CLR Stored Procedure JSON Parameter
body: <p>I've come across a scenario where I want to pass a large amount of data to a Stored Procedure for generating some dynamic SQL.</p>
  
  <p>The data I want to pass is stored in this Json/C# class object that I use in my ASP.NET MVC web project.</p>
  
  <pre><code>[
   {
    "code":"ABC123",
    "Count": "12998",
    "Params":
    [
      {"name": "Recent", "value": "0-12m"},
      {"name": "Orders", "value": "1"}
    ]
   },
   {
    "code":"ABC124",
    "Count": "13998",
    "Params":
    [
     {"name": "Recent", "value": "0-12m"},
     {"name": "Orders", "value": "2"}
    ]
   },
   {
    "code":"ABC125",
    "Count": "7998",
    "Params":
    [
     {"name": "Recent", "value": "0-12m"},
     {"name": "Orders", "value": "3"}
    ]
   }
  ]
  .....
  </code></pre>
  
  <p>Then use this text parameter to convert back to a JSON object, I use this in an action filter to convert it to an object.</p>
  
  <pre><code>public class ObjectFilter : ActionFilterAttribute
  {
  
      public string Param { get; set; }
  
      public Type RootType { get; set; }
  
      public override void OnActionExecuting(ActionExecutingContext filterContext)
      {
  
          if ((filterContext.HttpContext.Request.ContentType ?? string.Empty).Contains("application/json"))
          {
  
              object o =
  
              new DataContractJsonSerializer(RootType).ReadObject(filterContext.HttpContext.Request.InputStream);
              filterContext.HttpContext.Request.InputStream.Seek(0, SeekOrigin.Begin); // Rewind InputStream for other filters
  
              filterContext.ActionParameters[Param] = o;
  
          }
  
          else
          {
  
              var xmlRoot = XElement.Load(new StreamReader(filterContext.HttpContext.Request.InputStream,
  
              filterContext.HttpContext.Request.ContentEncoding));
  
              object o = new XmlSerializer(RootType).Deserialize(xmlRoot.CreateReader());
  
              filterContext.ActionParameters[Param] = o;
  
          }
  
      }
  </code></pre>
  
  <p>}</p>
  
  <p>and then use C# etc in my CLR Stored Procedure to create a SQL statement such as:</p>
  
  <pre><code>UPDATE [Sample]
  SET [Field] =
  CASE
  WHEN [Recent] = "0-12m" AND [Orders] = "1" THEN "ABC123"
  WHEN [Recent] = "0-12m" AND [Orders] = "2" THEN "ABC124"
  WHEN [Recent] = "0-12m" AND [Orders] = "3" THEN "ABC125"
  ...
  </code></pre>
  
  <p>Is this possible and has anyone ever done anything like this. I've seen a few posts on using a XML parameter but none using a varchar parameter using de-serialized (?) json.</p>