Previous Thread:   CREATE ASSEMBLY issue

3/1/2006 11:53:57 AM    CLR Aggregate Parameters
I read through the MSDN/SSBOL docs looking for examples of aggregates and  
  
did not find one that accepted more than one parameter.  
  
I have a CLR UDT for international currency. I want to aggregate the values  
  
and I could SELECT just those values of a particular currency beforehand,  
  
but does it make sense to add a parameter to the aggregate expression to  
  
specify a currency? For example  
  
SELECT CurrSum(myCurCol, 'GBP')  
  
to sum only those columns in British pounds.  
  
Don't get wrapped up in the example... the real question is, can an  
  
aggregate accept initialization or focusing parameters?  
  
--  
  
____________________________________  
  
William (Bill) Vaughn  
  
Author, Mentor, Consultant  
  
Microsoft MVP  
  
INETA Speaker  
  
www.betav.com/blog/billva  
  
www.betav.com  
  
Please reply only to the newsgroup so that others can benefit.  
  
This posting is provided "AS IS" with no warranties, and confers no rights.  
  
__________________________________



3/1/2006 9:10:39 PM    Re: CLR Aggregate Parameters
"William (Bill) Vaughn" <billvaRemoveThis@nwlink.com> wrote in message  
  
news:eN$fhnWPGHA.2628@TK2MSFTNGP15.phx.gbl...  
  
No.  
  
You can concatenate to force in more than one param.  But remember,  
  
multiple params -may- cause ambiguity.  In your example (only sum British  
  
pounds) I would use a CASE expression instead of trying to force  
  
parameterization.  
  
A more interesting multi-param example might be: SumOfCurrency(decimal  
  
Amount, string CountryCode), which converts all of the amounts to US dollars  
  
(or Euros, or some other common currency) internally before summing.  That  
  
one also wouldn't have ambiguity issues.  But consider:  Concatenate(string  
  
theString, int Order), where Order=0 is alphabetically ascending and Order=1  
  
is alphabetically descending.  In this case, one row in a group could pass  
  
in 0, while another passes in 1... That kind of ambiguity is important to be  
  
aware of when trying to do this.  
  
--  
  
Adam Machanic  
  
Pro SQL Server 2005, available now  
  
http://www.apress.com/book/bookDisplay.html?bID=457  
  
--