Friday, December 11, 2009

SQLCLR : Table Valued Function


using System;
using Microsoft.SqlServer.Server;
using System.Collections;
using System.Data.SqlTypes;
using System.Collections.Generic;

class Product
{
public int Quantity { get; set; }
public string Description { get; set; }
}


public partial class UserDefinedFunctions
{

[SqlFunction(TableDefinition = @"Quantity int,
Description nvarchar(100)",
Name = "Products",
FillRowMethodName = "FillRowProduct")]
public static IEnumerable GetProducts()
{

List lstPerson = new List();
lstPerson.Add(new Product { Quantity = 1, Description = "Ball" });
lstPerson.Add(new Product { Quantity = 10, Description = "Bike" });
lstPerson.Add(new Product { Quantity = 5, Description = "Club" });
lstPerson.Add(new Product { Quantity = 10, Description = "Bat" });
return lstPerson;
}


public static void FillRowProduct(Object obj, out SqlInt32 Quantity, out SqlChars Description)
{
Product p = (Product)obj;
Description = new SqlChars(p.Description);
Quantity = new SqlInt32(p.Quantity);
}
};

<--->
SELECT Prod.*
FROM dbo.Products() AS Prod

Quantity Description
1    Ball
10    Bike
5    Club
10    Bat

No comments:

Post a Comment