I had to do this very recently (last few days), and I ended up creating the stored procedure as a CLR. I wasn't sure how I felt about it at first, but it ended up working really well. I created the CLR in Visual Studio and was able to go from conception to realization in only a few hours.

Here's the code for the CLR ion C# (I ended up making it as a UDF so I could call it for every line):

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

using System.Net;

using System.IO;

using System.Text;

public partial class UserDefinedFunctions

{

[Microsoft.SqlServer.Server.SqlFunction]

public static SqlString ufnFunction(string variable)

{

string str = "";

string cn = "http://server/default.aspx?variable=" + variable;

HttpWebRequest http = (HttpWebRequest)

WebRequest.Create(cn);

http.KeepAlive = false;

HttpWebResponse resp = (HttpWebResponse)http.GetResponse();

Stream receiveStream = resp.GetResponseStream();

Encoding encode = System.Text.Encoding.GetEncoding("utf-8");

StreamReader readStream = new StreamReader(receiveStream, encode);

while (!readStream.EndOfStream)

{

str = str + readStream.ReadLine();

}

readStream.Close();

resp.Close();

return new SqlString(str);

}

};