How to store output of a stored procedure on to di

Question

We are using SQL Server 2005. I want to create the file of the output of the stored procedure.

Answer 1

If you are using MySQL you may want to start from the following:

DELIMITER $$

CREATE PROCEDURE export_dynamic(IN file_path char(64))
BEGIN
    SET @sql = CONCAT('SELECT * INTO OUTFILE ', 
                      "'", file_path, "'", ' FROM Table1');

    PREPARE stmt1 FROM @sql;
    EXECUTE stmt1;
    DEALLOCATE PREPARE stmt1;
END $$

DELIMITER ;

Borrowed from: MySQL Forums :: General :: SELECT .. INTO OUTFILE not possible with stored procedure parameters?

If you want to hardcode the filename in the stored procedure, then there is no need to use a prepared statement:

DELIMITER $$

CREATE PROCEDURE export_static()
BEGIN
    SELECT * INTO OUTFILE '/tmp/file_name.txt' FROM Table1;
END $$

DELIMITER ;

If you get a Can't create/write to file error, make sure to check the following reference: MySQL 5.1 Reference Manual :: Can't create/write to file.