How to pass input arguments from Database Toolbox R2015a to SQL script?

Hello,

I don't know the easiest way in which to explain this but I would like to pass a date variable that is generated by Matlab into my SQL script. The SQL script itself declares variables so I have found it very difficult to create some sort of dynamic relation between the two. Let me post the scripts to hopefully better clarify my issue:

Matlab Script is below. The key is the date variable I am creating which is effectively 45 days less today along with the runsqlscript function.

% Set preferences with setdbprefs.Using Table format.
setdbprefs('DataReturnFormat', 'Table');
setdbprefs('NullNumberRead', 'NaN');
setdbprefs('NullStringRead', 'null');

 % Make connection to database.  
% Using JDBC driver.
connect = database('Business', 'Test', 'Test', 'Vendor', 'MICROSOFT SQL SERVER', 'Server', 'Database', 'PortNumber', 1433, 'AuthType', 'Server');

% Set the Date Variable
Date = datestr(today - 45);

 % Read SQL Script RETURNS_BondABS.sql
results = runsqlscript(connect,'RETURNS_BondABS.sql');
returns= results.Data;

The SQL Script 'Returns_BondABS.sql' is rather long so I will post a snippet because the only thing I need to change is the line where @current = ',Date,' (or so I believe). You will see this variable declares quite a few variables but the only one that I need to change is @current. I would like to replace this with the Date variable matlab generates. The ultimate goal is create a loop in matlab on the date variable so as to run this sql script repeatedly for different dates.

DECLARE @current datetime,
        @prior_1d datetime,
        @prior_eom datetime,
        @cyr datetime,
        @prior_cy datetime

SELECT
     @current = ',Date,'
SET @prior_1d =  DATEADD(DAY,-1, @current)

SELECT
     XXXX,
     XXXX,
      ....

Let me know if I haven't been clear or if you require any more information. Running the script as is I generated the following error: 'Attempt to reference field of non-structure array' as well as this message in my 'results' cursor object: 'Conversion failed when converting date and/or time from character string.'

[Jan]

Duplicate thread removed. Please post a question once only. Thanks.

[Robert]

Accidental, didn't notice

Answer 1

[Gitesh Nandre]

SQL script does not accept input arguments. It needs to be modified as an SQL stored procedure to pass input arguments to it.

Input arguments can be passed to stored procedures by using one of the below mentioned approaches depending on the requirement:

1. If there is no need to display the modified table after running the stored procedure, 'runstoredprocedure' can be used with input argument(s)

2. If the modified table has to be displayed on the MATLAB Command Window after running the stored procedure, 'exec' command can be used as described in the documentation at:

http://www.mathworks.com/help/releases/R2015a/database/ug/run-a-stored-procedure-that-returns-data.html

[Robert]

Yes thankyou that is spot on!