How to Implement Excel COMBIN function in SQL

Question

How can i implement Excel COMBIN function in SQL 2005, either in a store procedure or in a function. About COMBIN function in Excel follow this Link.

Thanks.

Answer 1

1. Either implement a factorial function or depending on your possible input range, store factorial results in a table.

2. Implement the formula, as seen in your link.

Answer 2

No built-in method for doing this, you will have to make a custom user-defined function.

Rather than doing something crazy like a recursively factorial (as seen in this forum discussion), I would do the following:

• Create a factorial lookup table, holding pre-calculated factorial values (maybe 1! to 100!, since 100! is over 9.3 × 10^157), or as high as you think you might need.

• Then in your user-defined function, just look up n!, k!, and (n-k)! from the table, then calculate (n!) / (k!) * (n-k)!

Answer 3

Depending on what your possible input values are going to be, it may be worth pre-computing the values and storing them in a table; retrieving COMBIN(n, k) would then be as easy as

SELECT value FROM Combin WHERE n = @n AND k = @k

Answer 4

Precalculation does seem more sensible but a solution without.

declare @n int
declare @k int

SET @n = 8
SET @k = 2

SELECT 
    POWER(10.0, SUM(CASE WHEN Number > (@n - @k) THEN LOG10(Number) ELSE 0 END))/
    POWER(10.0, SUM(CASE WHEN Number <= @k THEN LOG10(Number) ELSE 0 END))
FROM master.dbo.spt_values
WHERE  type='P' AND
((Number > 0 and Number <= @k) OR (Number > (@n - @k) AND Number <= @n))