The same way as any other download, the client initiates the download by clicking a link/button and the source of the file is the output of the stored procedure. Alternatively you can use SSIS to generate the files beforehand and just serve a link to the files.

😎