CODEWITHSUNDEEP
c#sql-servert-sqlsqlclr
Ayman Elarian
Ayman Elarian

Reputation: 316

SQL CLR Access Temporary Table

i am new about SQL CLR , i will use to to reverse geocode temp table result

Planned Scenario will be

  1. Stored Procedure that create temporary table for Report Results
  2. Call SQL CLR Function that will fill Datatable from temporary table results
  3. i will do reverse reverse geocoding (reverse geocoding function implemented and working good) and fill new columns for location to this Datatable
  4. Return Datatable as new temporary table to my Stored Procedure so i will join results

So how to access Temp table from SQL CLR ?

Upvotes: 1

Views: 1816

Answers (2)

Solomon Rutzky
Solomon Rutzky

Reputation: 48826

This is a bit confusing, but it sounds like you want to read from a local temporary table (i.e. #TableName) for the purpose of passing in a set of values. In this case, you can SELECT from local temporary tables in SQLCLR objects if you use Context Connection = true for the Connection String.

If this is going to be a Table-Valued Function (TVF), then it should pass back the rows from the DataTable as the result set. Of course, you don't need to use a DataTable in either direction. You can process each input row and pass it back as it is being read in, before the next input row is read:

ResultsStruct _ResultSet = new ResultsStruct();

using (SqlConnection _Connection = new SqlConnection("Context Connection = true"))
{
  using (SqlCommand _Command = _Connection.CreateCommand())
  {
    _Command.CommandText = "SELECT Column1, Column2 FROM #ValuesToPassIn;";
    _Connection.Open();

    using (SqlDataReader _Reader = new _Command.ExecuteReader())
    {
      while(_Reader.Read())
      {
        var1 = _Reader.GetInt32(0);
        var2 = _Reader.GetInt32(1);

        ProcessStuff(var1, var2);

        _ResultSet.SetString(0, something1);
        _ResultSet.SetSomething(1, something2);

        yield return _ResultSet;
      }
    }
  }
}

The general structure of the T-SQL Stored Procedure would be:

CREATE TABLE #ValuesToPassIn
(
  Column1 DATATYPE,
  Column2 DATATYPE,
  ...
);

INSERT INTO #ValuesToPassIn (Column1, Column2, ...)
VALUES (val1a, val2a, ...), (val1b, val2b, ...);

CREATE TABLE #ValuesComingBack
(
  ColumnA DATATYPE,
  ColumnB DATATYPE,
  ...
);

INSERT INTO #ValuesComingBack (ColumnA, ColumnB, ...)
  SELECT ResultField1, ResultField2, ...
  FROM   dbo.SqlClrTableValuedFunction();

Or, for passing the set of values in, you can send them in as XML:

DECLARE @ValuesToPassIn XML;

SET @ValuesToPassIn = N'<vals>...</vals>';

CREATE TABLE #ValuesComingBack
(
  ColumnA DATATYPE,
  ColumnB DATATYPE,
  ...
);

INSERT INTO #ValuesComingBack (ColumnA, ColumnB, ...)
  SELECT ResultField1, ResultField2, ...
  FROM   dbo.SqlClrTableValuedFunction(@ValuesToPassIn);

For more information / details on working with SQLCLR, please see the series I am writing on SQL Server Central: Stairway to SQLCLR (please note: that site requires free registration to read articles).

Upvotes: 2

Ben Thul
Ben Thul

Reputation: 32697

If it were me, I'd write it as a table-valued function that takes (for instance the latitude and longitude) and returns the address (or vice versa if I've misunderstood). Then, you can call it in SQL like so:

insert into #t (Address)
select clr.Address
from dbo.yourTable
cross apply dbo.yourCLRFunction(latitude, longitude) as clr

At that point, the function doesn't need access to the table at all as all of the data will be passed to/through it.

Upvotes: 0

Related Questions