runstoredprocedure

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 Connect 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)

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 Connect 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)

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 Connect to Database. Then, call the stored procedure using:

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)

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 Connect 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)

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 Connect to Database. Then, call the stored procedure using:

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)