User`s guide

6 Using Database Toolbox Functions
6-46
Run a Custom Database Function
This example shows how to run a custom database function on Microsoft SQL Server.
Consider a database function get_prodCount that retrieves row counts in the table
productTable. The table productTable contains 30 rows where each row represents a
product. This code defines this database function and assumes a schema name dbo.
CREATE FUNCTION dbo.get_prodCount()
RETURNS int
AS
BEGIN
DECLARE @PROD_COUNT int
SELECT @PROD_COUNT = count(*) from productTable
RETURN(@PROD_COUNT)
END
GO
Create the Database Connection
Connect to Microsoft SQL Server. For example, this code assumes you are connecting to a
data source named MS SQL Server with user name username and password pwd.
conn = database.ODBCConnection('MS SQL Server','username','pwd');
Execute the Custom Function
Construct an SQL query string sqlquery that executes the custom function code.
Execute the custom function by running exec.
sqlquery = 'SELECT dbo.get_prodCount() as num_products';
curs = exec(conn,sqlquery);
curs = fetch(curs);
Display the result.
curs = fetch(curs);
curs.Data
ans =
[30.00]
The custom function get_prodCount returns the product count 30.