%% Call Stored Procedure Using SQL Prepared Statement
% Create an SQL prepared statement to call a stored procedure in a Microsoft® SQL Server® database using a JDBC database connection. Use the CALL SQL statement for the SQL query. Execute the SQL prepared statement and display the results.
% For this example, the SQL Server database contains the stored procedure getSupplierInfo, which returns the information for suppliers in a specified 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 SQL Server, the statement SET NOCOUNT ON suppresses the results of INSERT, UPDATE, and non-SELECT statements preceding the final SELECT query, so that you can import the results of the SELECT query.
%
% Create a JDBC database connection to an SQL Server database with Windows® authentication. Specify a blank user name and password.

datasource = "MSSQLServerJDBCAuth";
conn = database(datasource,'','');

%
% Create an SQL prepared statement for calling the stored procedure using the JDBC database connection. The question marks in the CALL SQL statement indicate it is an SQL prepared statement. This statement calls the getSupplierInfo stored procedure in the database.

query = "{CALL dbo.getSupplierInfo(?)}";
pstmt = databasePreparedStatement(conn,query)

% pstmt is an SQLPreparedStatement object with these properties:
% SQLQuery — SQL prepared statement query
% ParameterCount — Parameter count
% ParameterTypes — Parameter types
% ParameterValues — Parameter values
%
% Bind parameter values in the SQL prepared statement. Select the parameter in the SQL prepared statement using its numeric index. Specify the value to bind as the city New York. The bindParamValues function updates the values in the ParameterValues property of the pstmt object.

selection = [1];
values = "New York";
pstmt = bindParamValues(pstmt,selection,values)

%
% Display the results of the stored procedure. Execute the SQL CALL statement using the fetch function. The SQL prepared statement returns all information for suppliers located in New York City.

results = fetch(conn,pstmt)

%
% Close the SQL prepared statement and database connection.

close(pstmt)
close(conn)

% Copyright 2019 The MathWorks, Inc.