Run 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 Database Connection

Connect to Microsoft SQL Server using an ODBC driver. For example, this code assumes that you are connecting to a data source named MS SQL Server with user name username and password pwd.

conn = database('MS SQL Server','username','pwd');

Execute Custom Function

Construct an SQL query sqlquery that executes the custom function code. Execute the custom function and import the results by using the fetch function.

sqlquery = 'SELECT dbo.get_prodCount() as num_products';
results = fetch(conn,sqlquery);

Display the results. The custom function get_prodCount returns the product count 30.

results

results =

  table

    num_products
    ____________

         30

Close Database Connection

close(conn)