En ocasiones la complejidad para devolver un resultado con TSQL y el uso de Union o  consultas anidadas se vuelve muy complejo y es más sencillo para nosotros como programadores analizar cada fragmento de nuestro resultado o tomarlo de distintas fuentes y cálculos antes de devolverlo, creando así una tabla virtual o lo que anteriormente se conocía como un “cursor” en algunos otros mores de base de datos.

En SQL 2008 (incluso en 2005) se pueden crear funciones con C# o VB o sea programar .Net dentro de la base de datos, y esto nos ayuda a agregar funcionalidad y/o controlar una lógica especial dentro de la misma base de datos.

La idea es simple con SQLFunction se devuelve una estructura que Transact SQL tome como una tabla, similar a una vista, solo que aquí no son apuntadores ya son resultados calculados dentro de una tabla virtual o cursor. Quien consulte esta función no sabrá de donde viene los datos, solo la estructura y los registros resultados.

Lo primero es que voy a usar la clásica base de datos Northwind y luego con Visual Studio crearé un proyecto de base de datos SQL con CLR e inmediatamente después de guardar te muestra las conexiones disponibles, selecciona la Northwind
 

Nota importante: para poder depurar el proyecto debes iniciar el Visual Studio 2010 en modo administrativo y tu conexión con autentificación Windows; doy por asentado que el usuario con el que ejecutas tiene permisos administrativos en tu base de datos.

Así mismo tu SQL Server debe tener habilitado la ejecución de CLR, eso se logra simplemente ejecutando esta consulta:

exec sp_configure 'clr enabled','1'
GO
RECONFIGURE
GO

Creado tu proyecto agrega una función definida por el usuario:
 
y aquí es donde viene la “talacha” de la programación

1. Crea una clase con la estructura que deseas devolver (o sea la definición de los campos para tu tabla virtual)
2. Configura el Atributo Microsoft.SqlServer.Server.SqlFunction
   para indicar que vas a leer de la base de datos, la definición en T-SQL de tu tabla y qué método convierte cada registro a dicha definición
3. Crea un método que reciba un objeto y tenga como parámetros de salida cada uno de los campos de la clase estructura, convierte ese objeto a tu clase y devuelve cada parámetro con sus propiedades
4. Realiza todas las consultas y operaciones necesarias y guarda los resultados en una Lista genérica en base a la clase de la estructura
5. Devuelve la lista con los resultados

Bien eso sería todo, ¿cómo es esto en código?

1. Clase con la estructura que deseas devolver (o sea la definición de los campos para tu tabla virtual)

   public class EstructuraEspecial {
       public SqlString Region { get; set; }
       public SqlInt32 TotalPorRegion { get; set; }
       public SqlString Producto { get; set; }
       public SqlMoney TotalVentas { get; set; }
       public SqlMoney Promedio { get; set; }
   }

2. Configura el Atributo Microsoft.SqlServer.Server.SqlFunction
   para indicar que vas a leer de la base de datos, la definición en T-SQL de tu tabla y qué método convierte cada registro a dicha definición
   

   [Microsoft.SqlServer.Server.SqlFunction(DataAccess = DataAccessKind.Read,
           TableDefinition = "Region nvarchar(50),TotalPorRegion int, Producto nvarchar(40), TotalVentas money,Promedio money",
           FillRowMethodName = "LlenadoRegiostroConsulta")]
       public static IEnumerable ConsultaEspecial(int ProductoId) {
       

   ¿Qué hay en este código?
   -Bueno, comenzamos con la propiedad TableDefinition; esa define ya en lenguaje Transact SQL los mismos campos de tu estructura, para que dentro de una consulta, procedimiento almacenadoo y/o hasta vista, el motor de SQL sepa cómo mostrar la tabla resultado.
   -Luego el FillRowMethodName; es un método que se ejecuta cuando se devuelve la consulta, es decir, tu vas a devolver una Lista de elementos en C#, pero estos deben de ser convertidos a T-SQL, este método se llamará por cada elemento de la lista para convertirse a su equivalente en T-SQL
   -El tipo de dato devuelto tiene que ser un IEnumerable
   -Puedes poner tantos parámetros como necesites

3. Crea un método que reciba un objeto y tenga como parámetros de salida cada uno de los campos de la clase estructura, convierte ese objeto a tu clase y devuelve cada parámetro con sus propiedades.
   

   public static void LlenadoRegiostroConsulta(object obj,
       out SqlString Region,
       out SqlInt32 TotalPorRegion,
       out SqlString Producto,
       out SqlMoney TotalVentas,
       out SqlMoney Promedio) {
       EstructuraEspecial registro = (EstructuraEspecial)obj;
       Region = registro.Region;
       TotalPorRegion = registro.TotalPorRegion;
       Producto = registro.Producto;
       TotalVentas = registro.TotalVentas;
       Promedio = registro.TotalVentas / registro.TotalPorRegion; //registro.Promedio;
   }

   
   -Este método tienen por fuerza que recibir un objeto (será cada elemento de la lista resultado)
   -Debe tener un parámetro  de salida por cada campo de tu tabla definida en la función
   -Conviertes el tipo Object al tipo de tu estructura
   -Asignas cada propiedad a su respectiva columna y/o calculas los resultados de cada una como en el caso de la columna Promedio
   
4. Realiza todas las consultas y operaciones necesarias y guarda los resultados en una Lista genérica en base a la clase de la estructura
   

      1: [Microsoft.SqlServer.Server.SqlFunction(DataAccess = DataAccessKind.Read,

      2:     TableDefinition = "Region nvarchar(50),TotalPorRegion int, Producto nvarchar(40), TotalVentas money,Promedio money",

      3:     FillRowMethodName = "LlenadoRegiostroConsulta")]

      4: public static IEnumerable ConsultaEspecial(int ProductoId) {

      5:     var cnx = new SqlConnection("context connection=true");

      6:     var cmd = new SqlCommand(@"SELECT DISTINCT Region.RegionDescription

      7:                                 FROM Orders INNER JOIN

      8:                                         EmployeeTerritories INNER JOIN

      9:                                         Employees ON EmployeeTerritories.EmployeeID = Employees.EmployeeID INNER JOIN

     10:                                         Territories ON EmployeeTerritories.TerritoryID = Territories.TerritoryID INNER JOIN

     11:                                         Region ON Territories.RegionID = Region.RegionID ON Orders.EmployeeID = Employees.EmployeeID INNER JOIN

     12:                                         [Order Details] ON Orders.OrderID = [Order Details].OrderID INNER JOIN

     13:                                         Products ON [Order Details].ProductID = Products.ProductID

     14:                                 WHERE (Products.ProductID = @ProductID)", cnx);

     15:     cmd.Parameters.AddWithValue("@ProductID", ProductoId);

     16:  

     17:     cnx.Open();

     18:     var dr = cmd.ExecuteReader();

     19:     var regiones = new List<string>();

     20:     while (dr.Read()) {

     21:         regiones.Add(dr[0].ToString());

     22:     }

     23:     dr.Close();

     24:  

     25:     var tablaResultado = new List<EstructuraEspecial>();

     26:  

     27:     foreach (var region in regiones) {

     28:         var cmdTotalPorRegion = new SqlCommand(@"SELECT COUNT(Region.RegionDescription) AS TotalOrdenesPorRegion

     29:                                                     FROM Orders INNER JOIN

     30:                                                             EmployeeTerritories INNER JOIN

     31:                                                             Employees ON EmployeeTerritories.EmployeeID = Employees.EmployeeID INNER JOIN

     32:                                                             Territories ON EmployeeTerritories.TerritoryID = Territories.TerritoryID INNER JOIN

     33:                                                             Region ON Territories.RegionID = Region.RegionID ON Orders.EmployeeID = Employees.EmployeeID INNER JOIN

     34:                                                             [Order Details] ON Orders.OrderID = [Order Details].OrderID INNER JOIN

     35:                                                             Products ON [Order Details].ProductID = Products.ProductID

     36:                                                     WHERE (Products.ProductID = @ProductID) AND (Region.RegionDescription = @Region)", cnx);

     37:         cmdTotalPorRegion.Parameters.AddWithValue("@ProductID", ProductoId);

     38:         cmdTotalPorRegion.Parameters.AddWithValue("@Region", region);

     39:         var total = cmdTotalPorRegion.ExecuteScalar();

     40:         var e = new EstructuraEspecial();

     41:         e.Region = region;

     42:         e.TotalPorRegion = SqlInt32.Parse(total.ToString());

     43:         tablaResultado.Add(e);

     44:     }

     45:  

     46:     var cmdProducto = new SqlCommand(@"SELECT     ProductName, UnitPrice

     47:                                         FROM         Products

     48:                                         WHERE     (ProductID = @ProductID)", cnx);

     49:     cmdProducto.Parameters.AddWithValue("@ProductID", ProductoId);

     50:     dr = cmdProducto.ExecuteReader();

     51:     SqlString producto = string.Empty; SqlMoney precio = 0;

     52:     if (dr.Read()) {

     53:         producto = dr["ProductName"].ToString();

     54:         precio = SqlMoney.Parse(dr["UnitPrice"].ToString());

     55:     }

     56:     dr.Close();

     57:  

     58:     cnx.Close();

     59:  

     60:     foreach (var res in tablaResultado) {

     61:         res.Producto = producto;

     62:         res.TotalVentas = res.TotalPorRegion * precio;

     63:         res.Promedio = res.TotalVentas / res.TotalPorRegion;

     64:     }

     65:  

     66:     return tablaResultado.ToArray();

     67: }

   Aquí las líneas de código sobre salientes son:
   -25: Declaración de la tabla de resultados como Lista genérica en base a la estructura declarada
   -40 a 43: Instanciación de un elemento de la lista y agregarlo a la tabla resultado
   -60 a 64: Terminar de calcular el resto de las propiedades de la clase
   
5. Devuelve la lista con los resultados
   
   - En la línea 66: Automáticamente se ejecuta el método “LlenadoRegistroConsulta” por cada elemento de la lista para convertirlo a su correspondiente en T-SQL
   

Listo ahora simplemente mete tu código compilado a la BD con el botón secundario sobre tu proyecto –> Distribuir

Para ejecutar la función desde SQL Server:

• La puedes llamar directamente en una sentencia Select pasandole el valor de los parámetros que requieras:
  

  SELECT * FROM dbo.ConsultaEspecial(1)
  GO

• La puedes llamar desde una Vista, solo que aquí siempre te devolverá los resultados del mismo parámetro, dado que a la vista no le puedes pasar parámetros:
  

  CREATE VIEW vConsultaEspecial
  AS
  SELECT     Region, TotalPorRegion, Producto, TotalVentas, Promedio
  FROM         dbo.ConsultaEspecial(1) AS ConsultaEspecial
  
  GO

• O Crear un procedimiento almacenado que devuelva el resultado de acuerdo al parámetro dado:
  

  CREATE PROCEDURE ConsultarEspecialmente
      @ProductID int
  AS
  BEGIN
          SELECT     Region, TotalPorRegion, Producto, TotalVentas, Promedio
          FROM         dbo.ConsultaEspecial(@ProductID) AS ConsultaEspecial
  
  END
  
  GO

• Incluso usarla dentro de un union/join o cualquier instrucción T-SQL como si fuera una tabla cualquiera en SQL:
  

  SELECT Region.RegionID, ConsultaEspecial.*
  FROM Region INNER JOIN
        dbo.ConsultaEspecial(@ProductoID) AS ConsultaEspecial 
        ON Region.RegionDescription = ConsultaEspecial.Region

• Para usarlo desde Entity Framework, necesariamente tendrás que llamarla mediante una vista y/o un procedimiento almacenado, ya que hasta la versión 4 no soporta la ejecución directa de funciones que devuelvan tablas.

Espero te sea muy útil saludos, puedes bajar el código fuente de aquí.

Benji.Net