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)
