User`s guide

6 Using Database Toolbox Functions
6-42
Call a Stored Procedure That Returns Data
This example shows how to call a stored procedure that returns data using the exec
function. Use the JDBC interface to connect to a Microsoft SQL Server database,
call a stored procedure, and return data. For this example, the stored procedure
getSupplierInfo is defined in the Microsoft SQL Server database. 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 suppliers where city = @cityName
END
GO
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 fetch the results of the SELECT query.
Use exec when the stored procedure returns one or more result sets. For procedures that
return output parameters, use runstoredprocedure.
Create the Database Connection
Using the JDBC interface, connect to the Microsoft SQL Server database called
'test_db' with the user name 'root' and password 'matlab' using port number
1234. This example assumes your database server is located on the machine
servername.
conn = database('test_db','root','matlab',...
'Vendor','Microsoft SQL Server',...
'Server','servername','PortNumber',1234)
conn =
Instance: 'test_db'
UserName: 'root'