How to export resultset of a procedure with parameters using T-SQL command in SQL Azure database

Question

I have to export the results of a stored procedure with 2 input parameters in SQL Azure instance using a T-SQL command to a .csv file in SSMS. Thing is, I need to run this stored procedure 3000+ times in a while loops by changing the parameters. Hence I don't know if I can use Import-Export Wizard.

DECLARE @ReviewerId INT
SET @ReviewerId = ''

WHILE @ReviewerId IS NOT NULL
BEGIN
    SELECT @ReviewerId = MIN(Id) 
    FROM @Reviewers 
    WHERE Id > @ReviewerId

    IF @ReviewerId IS NOT NULL
    BEGIN
        DECLARE @ReviewerAlias nvarchar(100)
        DECLARE @ReviewIdentifier nvarchar(100) = 'SomeGUID'

        SELECT @ReviewerAlias = ReviewerAlias 
        FROM @Reviewers 
        WHERE Id = @ReviewerId

        PRINT N'Working on '+CAST(@ReviewerId AS VARCHAR)+': '+@ReviewerAlias+''; 
        -- Need to export the result of below command to .csv file.
        EXEC MyDatabaseName.dbo.MyStoredProcedure @reviewer = @ReviewerAlias, @reviewIdentifier = @ReviewIdentifier
    END
END

I googled a bit and found below command. But SQL Azure Instance does not have xp_cmdshell in master database. How do I proceed further?

DECLARE @sql VARCHAR(8000);

SELECT @sql = 'bcp "MyDatabaseName.dbo.MyStoredProcedure ''Parameter1'',''Parameter2''" queryout "V:\MyDocuments\SourceType.csv" -c -t, -T  -S ' + @@Servername;

EXEC master..xp_cmdshell @sql;

Answer 1

There is no command prompt available with SQL Azure.

Consider SQL Azure as nothing more than a virtualized service absent of host infrastructure (either physical or virtual).

You won't be able to access BCP from Azure because it's truly a command line program. On a computer that does have access to the command line, you should set up BCP, one of the command line utilities.

The work around for this is as follows:

• Set up an Azure SQL Server VM (IaaS) to execute yor requirement. You can find xp_cmdshell available on Azure SQL Server VM (IaaS).
• You can use data factory to store the stored procedure data into the File in on-premises.

IF you are using command line to execute you can use below command:

bcp "exec databas-ename.schema-name.SP-name 'Param1','Param2'" queryout "path of file" -S servername.database.windows.net -d dbname -U  username -G -c -t","

Answer 2

The problem with your code is there are multiple result sets. If you save results of your stored proecedure in a table variable or into a temporary table with INSERT INTO ... EXEC, than you could use Export data... task.

These is a little example:

DECLARE @result TABLE (Col1 INT, Col2 INT);

INSERT INTO @result 
EXEC Test 1, 2;

INSERT INTO @result 
EXEC Test 2,3;

SELECT * FROM @result;

In the Export data... wizard you need to use Write a query to specify the data transfer option in Specify Table Copy or Query step after choosing Flat file as destination.

I tried it regards a SQL Server Instance on Azure.