Linker IT Software
Google
Web www.oraxcel.com
menubar-top-links menubar-top-rechts
Home Help Search Login
Welcome, Guest. Please Login.
SQL*XL: Database to Excel bridge litLIB: Excel power functions pack ExcelLock: Locking and securing your valuable Excel spreadsheets encOffice: Protect your Excel file easy and safe encOffice: Protect your Excel file easy and safe
Pages: 1
Coupling Excel to SQL Server functions (Read 8028 times)
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Coupling Excel to SQL Server functions
30.12.09 at 19:18:23
 
Coupling Excel to SQL Server functions
 
The SQL*XL function SQLFunction allows you to couple a cell to a database function. The return value is shown in the cell. This technique already worked well with Oracle databases. Now it was also developed for SQL Server databases.
 
Example:
Type the following formula in a cell: =SQLFunction("dbo.myfuction")
 
To make it easy to try this out for yourself I have prepared a few test function that you can easily expand to do whatever you require it to do:
 
Returning a number:
 
=SQLFunction("dbo.f_int")
 
Code:
CREATE FUNCTION [dbo].[f_int]()
RETURNS integer
AS BEGIN
	RETURN 1;
END
 


 
Returning a text string:
 
=SQLFunction("dbo.f_varchar")
 
Code:
CREATE FUNCTION [dbo].[f_varchar]()
RETURNS varchar(15)
AS BEGIN
	RETURN 'Hello World';
END
 


 
Returning a date:
 
=SQLFunction("dbo.f_datetime")
 
Code:
CREATE FUNCTION [dbo].[f_datetime]()
RETURNS datetime
AS BEGIN
	RETURN getdate();
END
 


 
SQLFunction for SQL Server databases will become availalble in SQL*XL 4.3.21
Back to top
 
« Last Edit: 30.12.09 at 19:27:02 by Gerrit-Jan Linker »  

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Pass Excel parameters to SQL Server functions
Reply #1 - 02.01.10 at 13:48:36
 
Pass Excel parameters to SQL Server functions
 
To pass cell values to SQL Server (or Oracle) stored functions you can use the SQL*XL's SQLFunction Excel function. The syntax is simpel:
 
=SQLFunction("functionname",param1, param2, ....)
 
Examples:
A1: 10
C1: =SQLFunction("dbo.f_int",A1)
 
A10: "Hello World"
C10: =SQLFunction("dbo.f_varchar",A10)
 
A20: 01-01-2010
C20: =SQLFunction("dbo.f_datetime",A20)
 
The database functions used to test:
Code:
ALTER FUNCTION [dbo].[f_int](@p_int integer)
RETURNS integer
AS BEGIN
	RETURN @p_int;
END
 


 
Code:
ALTER FUNCTION [dbo].[f_varchar](@p_varchar varchar(20))
RETURNS varchar(20)
AS BEGIN
	RETURN @p_varchar;
END
 


ALTER FUNCTION [dbo].[f_datetime](@p_datetime datetime)
RETURNS datetime
AS BEGIN
     RETURN @p_datetime;
END
Code:

 

Back to top
 
« Last Edit: 02.01.10 at 13:51:58 by Gerrit-Jan Linker »  

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
rhoro
YaBB Newbies
*


Ack!

Posts: 1
Re: Coupling Excel to SQL Server functions
Reply #2 - 18.02.10 at 17:26:40
 
Is it also possible to setup an array function across a series of columns, which calls a SS function that returns a table with the same number of columns?
Back to top
 
 
Email   IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Re: Coupling Excel to SQL Server functions
Reply #3 - 19.02.10 at 08:27:36
 
Yes that is possible. I would recommend you to use the SQLQueryValue function. You just enter the SQL it should execute and that could of course contain function calls. SQL*XL will determine the number of columns and rows that you have selected and only display those details in the cells.
 
Further reading:
 
SQLQueryValue videa demo
http://www.oraxcel.com/projects/sqlxl/demos/database_query_cell_formula.html
 
SQLQueryValue help page
http://www.oraxcel.com/projects/sqlxl/help/functions/sqlqueryvalue.html
 
Back to top
 
 

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Pages: 1