Call Stored Procedure That Returns Data

This example shows how to call a stored procedure that returns data using the fetch function. Use the JDBC interface to connect to a Microsoft^® SQL Server^® database, call a stored procedure, and return data. For this example, the Microsoft SQL Server database contains the stored procedure getSupplierInfo. This stored procedure returns the supplier information for suppliers of a given city. This code defines the procedure.

CREATE PROCEDURE dbo.getSupplierInfo
	(@cityName varchar(20))
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON

    SELECT * FROM dbo.suppliers WHERE City = @cityName
END

For Microsoft SQL Server, the statement 'SET NOCOUNT ON' suppresses the results of INSERT, UPDATE, or any non-SELECT statements that might be before the final SELECT query, so you can import the results of the SELECT query.

Use the fetch function when the stored procedure returns one or more result sets. For procedures that return output parameters, use runstoredprocedure.

Create Database Connection

Using the JDBC interface, connect to the Microsoft SQL Server database called 'test_db' with a user name and password using port number 1234. This example assumes that your database server is on the machine servername.

conn = database('test_db','username','pwd', ...
    'Vendor','Microsoft SQL Server', ...
    'Server','servername','PortNumber',1234);

Call Stored Procedure

Call the stored procedure, getSupplierInfo, and display the supplier information for New York city using the fetch function and the database connection. results contains the supplier information.

sqlquery = '{call getSupplierInfo(''New York'')}';
results = fetch(conn,sqlquery)

ans =

  3×5 table

    SupplierNumber       SupplierName          City           Country          FaxNumber   
    ______________    __________________    __________    _______________    ______________

    1001              'Wonder Products'     'New York'    'United States'    '212 435 1617'
    1006              'ACME Toy Company'    'New York'    'United States'    '212 435 1618'
    1012              'Aunt Jemimas'        'New York'    'USA'              '14678923104' 

Close Database Connection

close(conn)