Accessing Excel functions in SQL SP

Hi,
I am trying to access an Excel function in a stored procedure. First of all,
is it possible, if it is, can someone give me an example.
Thank you.
--
RamIt may be possible with sp_OAMethod, but why do it to yourself? Even if you
can get to work without blowing up your computer, performance is going to be
rubbish!
SQL has loads of functions, and you can roll your own, do you know about
user-defined functions? They're great!
Anyway, tell us which function you want to emulate and we'll see what we can
do.
Plus, also bear in mind, SQL might have the function you need, but you just
don't know it's name; eg MID in Excel is called SUBSTRING in T-SQL, there's
a
ROUND function, CHARINDEX is the same as FIND in Excel, if you need to do
some conditional logic, there's the CASE statement instead of Excel's IF.
Remember to post some DDL, sample data, expected results etc.
Damien
First of all, there are few things you can't do in SQL
"ram4tech" wrote:

> Hi,
> I am trying to access an Excel function in a stored procedure. First of al
l,
> is it possible, if it is, can someone give me an example.
> Thank you.
> --
> Ram|||Hi Damien:
The looked in BOL and on the net, but didn't had much luck. I wasn't aware
that there might be some performance issues. The excel function I am plannin
g
on using is IRR().
Thank you.
--
Ram
"Damien" wrote:
> It may be possible with sp_OAMethod, but why do it to yourself? Even if y
ou
> can get to work without blowing up your computer, performance is going to
be
> rubbish!
> SQL has loads of functions, and you can roll your own, do you know about
> user-defined functions? They're great!
> Anyway, tell us which function you want to emulate and we'll see what we c
an
> do.
> Plus, also bear in mind, SQL might have the function you need, but you jus
t
> don't know it's name; eg MID in Excel is called SUBSTRING in T-SQL, there'
s a
> ROUND function, CHARINDEX is the same as FIND in Excel, if you need to do
> some conditional logic, there's the CASE statement instead of Excel's IF.
> Remember to post some DDL, sample data, expected results etc.
> Damien
> First of all, there are few things you can't do in SQL
> "ram4tech" wrote:
>|||Hello Ram !
http://groups.google.de/group/micro...5d4e46703ec82cd
HTH, jens Suessmeyer.|||Right, well you'd need to know the formula that sits behind IRR to recreate
it, but I guess it boils down to addition, subtraction, maybe an average or
two? T-SQL can do all that, but Excel is better at sums, I'll give it that.
So maybe you should play to the strengths, ie SQL for holding data,
concurrent access, raw power, Excel for sums.
Have you considered linking in to your server, eg a pivot table or external
query?
"ram4tech" wrote:
> Hi Damien:
> The looked in BOL and on the net, but didn't had much luck. I wasn't aware
> that there might be some performance issues. The excel function I am plann
ing
> on using is IRR().
> Thank you.
> --
> Ram
>
> "Damien" wrote:
>|||Here is a T-SQL implementation of IRR I posted a while back.
It is probably less robust than the Excel version, but it may still
work for you:
http://groups.google.co.uk/groups?q...eam+kass+newton
Steve Kass
Drew University
ram4tech wrote:

>Hi Damien:
>The looked in BOL and on the net, but didn't had much luck. I wasn't aware
>that there might be some performance issues. The excel function I am planni
ng
>on using is IRR().
>Thank you.
>