OUTPUT statement in a Stored Procedure

[former_member1335322]

I am trying to put an output statement in a stored procedure so that I can export data to a text file easily whenever I need to.

My syntax looks something like this:

create procedure TestExportInventoryMaster () 
begin 


select * 
from TestTable; 
output to 'FileLocation\\ExportData.txt'; 

end

When I run the output statement, it exports all of the data that I want. However, when I try to create a stored procedure with an OUTPUT statement, I get an error message. "Syntax error near "OUTPUT"...SQL Code -131 ODBC 3 State 42000

Why is this happening?

[former_member1335322]

It seems like I cannot have output statements in procedures, read statements, or events.

Why is that so? Is there a way around this?

[chris_keating]

OUTPUT is an Interactive SQL statement. Such statements are only valid in Interactive SQL. Procedures and events are not run in Interactive SQL but the database engine. The READ statement is also an Interactive SQL statement which reads SQL statements from a file and executes those statements. If that file contains a valid use of OUTPUT, it should work.

[chris_keating]

Please also refer to http://dcx.sap.com/index.html#sqla170/en/html/817adec96ce21014abeedc732b220dbe.html which discusses how to determine where statements can be used.

[former_member182948]

These SELECT and OUTPUT can be replaced by UNLOAD statement.

e.g.
UNLOAD TABLE TestTable TO 'FileLocation\\ExportData.txt'

For detailed, see the below.
UNLOAD statementhttp://dcx.sap.com/index.html#sqla170/en/html/817fd0fd6ce21014a58ff727fbb7b4e2.html

[chris_keating]

OUTPUT is only valid in Interactive SQL. In a procedure, you can use the UNLOAD statement.