CLR Procedure

Here is the SQL for the CLR procedure:

---

Select LocationName as [Location],
Sum(Employee.func_PriceWithDiscount(AutoServiceID,DiscountPercent)) as Subtotal,
Sum(Employee.GetTax(Employee.func_PriceWithDiscount(AutoServiceID,DiscountPercent),TaxPercent))
as Tax,
sum(Employee.func_PriceWithDiscount(AutoServiceID,DiscountPercent) +
Employee.GetTax(Employee.func_PriceWithDiscount(AutoServiceID,DiscountPercent),TaxPercent))
as Total
From Employee.VehicleServiceDetail vsd
inner Join Employee.VehicleService vs
on vs.VehicleServiceID=vsd.VehicleServiceID
inner Join Customer.Location l
on l.LocationID=vs.LocationID
Group by LocationName

---

Here is the CLR code with the SQL embedded:

---

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;


public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void usp_LocationSales()
{
// Put your code here
using (SqlConnection connect = new SqlConnection("context connection=true"))
{
connect.Open();
SqlCommand cmd = connect.CreateCommand();
cmd.CommandText = "Select LocationName as [Location], "
+ "Sum(Employee.func_PriceWithDiscount(AutoServiceID,DiscountPercent)) as Subtotal, "
+ "Sum(Employee.GetTax(Employee.func_PriceWithDiscount(AutoServiceID,DiscountPercent),TaxPercent)) "
+ " as Tax, "
+ " sum(Employee.func_PriceWithDiscount(AutoServiceID,DiscountPercent) "
+ "+ Employee.GetTax(Employee.func_PriceWithDiscount(AutoServiceID,DiscountPercent),TaxPercent)) "
+ "as Total "
+ "From Employee.VehicleServiceDetail vsd "
+ "inner Join Employee.VehicleService vs "
+ "on vs.VehicleServiceID=vsd.VehicleServiceID "
+ "inner Join Customer.Location l "
+ "on l.LocationID=vs.LocationID "
+ "Group by LocationName ";

SqlContext.Pipe.ExecuteAndSend(cmd);
}
}
};