runstoredprocedure

Call stored procedure with and without input and output arguments

Syntax

results = runstoredprocedure(conn,spname)

results = runstoredprocedure(conn,spname,inputargs)

results = runstoredprocedure(conn,spname,inputargs,outputtypes)

Description

This function calls a stored procedure that has no input arguments, no output arguments, or any combination of input and output arguments. Define and instantiate this stored procedure in your database.

You can use this function if you connect to your database using a JDBC driver. For details, see Connecting to Database. If you are using the native ODBC interface to connect to your database, use exec to call the stored procedure.

results = runstoredprocedure(conn,spname) calls the stored procedure spname using the database connection conn. results is a logical 1 if the stored procedure returns a data set. Otherwise, results is a logical 0.

results = runstoredprocedure(conn,spname,inputargs) calls the stored procedure that accepts one or more input arguments inputargs.

results = runstoredprocedure(conn,spname,inputargs,outputtypes) calls the stored procedure that returns output arguments by specifying the output argument data types outputtypes. results is a cell array that contains one or more output arguments.

Examples

Call a Stored Procedure Without Input and Output Arguments

Define a stored procedure named create_table that creates a table named test_table by executing this code. This procedure has no input or output arguments. This code assumes you are using a Microsoft^® SQL Server^® database.

CREATE PROCEDURE create_table 
	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

CREATE TABLE test_table
	 (
		CATEGORY_ID     INTEGER     IDENTITY PRIMARY KEY,
		CATEGORY_DESC   CHAR(50)    NOT NULL
        );
	
END
GO

Create a Microsoft SQL Server database connection conn using the JDBC driver. For details, see Connecting to Database. Then, call the stored procedure create_table using the database connection conn.

results = runstoredprocedure(conn,'create_table')

results = 

   0

results returns 0 because calling create_table does not return a data set.

Check your database for a new table named test_table.

Close the database connection conn.

close(conn)

Call a Stored Procedure with Input Arguments

Define a stored procedure named insert_data that inserts a category description into a table named test_create by executing this code. This procedure has one input argument data. This code assumes you are using a Microsoft SQL Server database.

CREATE PROCEDURE insert_data 
	@data varchar(50)

AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	INSERT INTO test_create (CATEGORY_DESC) 
	VALUES (@data)
END
GO

Create a Microsoft SQL Server database connection conn using the JDBC driver. For details, see Connecting to Database. Then, call the stored procedure insert_data using the database connection conn with the category description Apples as the input argument.

inputarg = {'Apples'};

results = runstoredprocedure(conn,'insert_data',inputarg)

results =

   0

results returns 0 because calling insert_data does not return a data set.

The table test_create adds a row where the column CATEGORY_ID equals 1 and the column CATEGORY_DESCRIPTION equals Apples.

CATEGORY_ID is the primary key of the table test_create. This primary key increments automatically. CATEGORY_ID equals 1 when calling insert_data for the first time.

Close the database connection conn.

close(conn)

Call a Stored Procedure with Output Arguments

Define a stored procedure named maxDecVolume that selects the maximum sales volume in December by executing this code. This procedure has one output argument data and no input arguments. This code assumes you are using a Microsoft SQL Server database.

CREATE PROCEDURE maxDecVolume
	@data int OUTPUT 
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

 	SELECT @data = max(December) FROM salesVolume
END

GO

Create a Microsoft SQL Server database connection conn using the JDBC driver. For details, see Connecting to Database. Then, call the stored procedure using:

• Database connection conn

• Stored procedure maxDecVolume

• Empty brackets to denote no input arguments

• Numeric Java^® data type outputtype

outputtype = {java.sql.Types.NUMERIC};

results = runstoredprocedure(conn,'maxDecVolume',[],outputtype)

results = 

    [1x1 java.math.BigDecimal]

results returns a cell array that contains the maximum sales volume as a Java decimal data type.

Display the value in results.

results{1}

ans =
 
35000 

The maximum sales volume in December is 35,000.

Close the database connection conn.

close(conn)

Call a Stored Procedure with Input and Output Arguments

Define a stored procedure named getSuppCount that counts the number of suppliers for a specified city by executing this code. This procedure has one input argument cityName and one output argument suppCount. This code assumes you are using a Microsoft SQL Server database.

CREATE PROCEDURE getSuppCount 
	(@cityName varchar(20),
	 @suppCount int OUTPUT)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	
	SELECT @suppCount = count(supplierNumber)
 FROM suppliers WHERE City = @cityName;
	
END
GO

Create a Microsoft SQL Server database connection conn using the JDBC driver. For details, see Connecting to Database. Then, call the stored procedure getSuppCount using the database connection conn. The input argument inputarg is a cell array containing the character vector 'New York'. The output Java data type outputtype is numeric.

inputarg = {'New York'};
outputtype = {java.sql.Types.NUMERIC};

results = runstoredprocedure(conn,'getSuppCount',inputarg,outputtype)

results = 

    [1x1 java.math.BigDecimal]

results is a cell array that contains the supplier count as a Java decimal data type.

Display the value in results.

results{1}

ans =
 
6.0000

There are six suppliers in New York.

Close the database connection conn.

close(conn)

Call a Stored Procedure with Multiple Input and Output Arguments

Define a stored procedure named productsWithinUnitCost that returns the product number and description for products that have a unit cost in a specified range by executing this code. This procedure has two input arguments minUnitCost and maxUnitCost. This procedure has two output arguments productno and productdesc. This code assumes you are using a Microsoft SQL Server database.

CREATE PROCEDURE productsWithinUnitCost
	(@minUnitCost INT, 
	@maxUnitCost INT,
	@productno INT OUTPUT, 
	@productdesc VARCHAR(50) OUTPUT)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

 SELECT @productno = productNumber, @productdesc = productDescription
 FROM productTable
	WHERE unitCost > @minUnitCost AND unitCost < @maxUnitCost
END


GO

Create a Microsoft SQL Server database connection conn using the JDBC driver. For details, see Connecting to Database. Then, call the stored procedure using:

• Database connection conn

• Stored procedure productsWithinUnitCost

• Input arguments inputargs to specify a unit cost between 19 and 21

• Output Java data types outputtypes to specify numeric and string data types for product number and description

inputargs = {19,21};
outputtypes = {java.sql.Types.NUMERIC,java.sql.Types.VARCHAR};

results = runstoredprocedure(conn,'productsWithinUnitCost',...
                             inputargs,outputtypes)

results = 

    [1x1 java.math.BigDecimal]
    'Snacks'                  

results returns a cell array that contains the product number as a Java decimal data type and the product description as a string.

Display the product number in results.

results{1}

ans =
 
15

The product with product number 15 has a unit cost between 19 and 21.

Display the product description in results.

results{2}

ans =

Snacks

The product with product number 15 has the product description Snacks.

Here, the narrow unit cost range returns only one product. If the unit cost range is wider, then more than one product might satisfy this condition. To return a data set with numerous products, use exec and fetch to call this stored procedure. Otherwise, runstoredprocedure returns only the last row in the data set.

Close the database connection conn.

close(conn)

Input Arguments

conn — Database connection

connection object

Database connection, specified as an ODBC connection object or JDBC connection object created using the database function.

spname — Stored procedure name

character vector

Stored procedure name, specified as a character vector that contains the name of the stored procedure that is defined and instantiated in your database.

Data Types: char

inputargs — Input arguments

cell array

Input arguments, specified as a cell array of one or more values for each input argument of the stored procedure. Input arguments can be only basic data types such as double, character vector, logical, and so on.

Data Types: cell

outputtypes — Output types

cell array

Output types, specified as a cell array of one or more Java data types for the output arguments of the stored procedure. Some JDBC drivers do not support all java.sql.Types. Consult your JDBC driver documentation to find the supported types. Match them to the data types found in your stored procedure.

Example: {java.sql.Types.NUMERIC}

Data Types: cell

Output Arguments

results — Stored procedure results

logical | cell array

Stored procedure results, returned as a logical or cell array.

runstoredprocedure returns a logical 1 when calling the stored procedure returns a data set. Otherwise, runstoredprocedure returns a logical 0. If the stored procedure returns a data set, use exec and fetch to call the stored procedure and retrieve the data set. For details, see Call Stored Procedure That Returns Data.

runstoredprocedure returns a cell array when you specify one or more output Java data types for the output arguments of the stored procedure. Use cell array indexing to retrieve the output argument values.