How can I execute a complex set of SQL commands at once with Database Toolbox?

I am using Database Toolbox to retrieve data from a Microsoft SQL Server. My query is very complex and can not be accomplished with a single SELECT statement. I have written a batch which I can execute from Query Analyzer; however when I use the EXEC command in MATLAB, no data is returned.

As a simple example, the following command

s = 'declare @id as int;set @id = 2;select * from testtbl where id = @id'
e=exec(conn,s)
e.Message

results in the message ERROR: No ResultSet was produced

Answer 1

[MathWorks Support Team]

The EXEC command only returns a result set if the first SQL command is SELECT or the name of a stored procedure. Since the above example began with a declaration, no result set was returned.

There are a number of ways to accomplish this task. Some of these options include:

1. Save the batch of SQL commands as a stored procedure in the database.

2. Use one EXEC command to prepare the data and store it into a temporary table. Then retrieve the data from the temporary table with a simple SELECT statement.

3. Use the database's built-in stored procedure for evaluating a string as SQL commands. For example, Microsoft SQL Server has a built-in stored procedure called sp_executesql. This function will execute any string and return the results of the command to Database toolbox. For example, in Microsoft SQL Server semicolons (;) are used to separate statements so you can execute a batch program as follows:

>> s = 'sp_executesql "declare @id as int;set @id = 2;select * from testtbl where id = @id"'
>> e = exec(conn,s)
>> e = fetch(e)
>> e.Data

4. Another possible workaround is to use 'runsqlscript' command. Create a .sql file with list of SQL statements separated by ';'. For example, .sql file can look like below.

/* .sql file begins */

declare @supplierNumber as int;
set @supplierNumber = 1000;
select * from suppliers where supplierNumber = @supplierNumber;
/* .sql file ends */

>> conn = database('test','<username>','<password>');
>> results = runsqlscript(conn,'test.sql')
>> close (results)
>> close(conn)