BCP command execute each row SQL Command in Table

Question

I had write a procedure that export each of the SQL Query data to the csv file with header. But I dont know the error shown as below.

Msg 512, Level 16, State 1, Line 26 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. Msg 512, Level 16, State 1, Line 27 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Here with my sample database

Here with my Code

DECLARE @SQLCmd varchar(max)
    DECLARE @FilePathCmd varchar(max)
    DECLARE @ExportCmd varchar(max)

    SET @SQLCmd=(SELECT CommandText from TestDB.dbo.CommandExportData)
    SET @FilePathCmd=(SELECT FilePath from TestDB.dbo.CommandExportData)

    

   SELECT @ExportCmd='bcp "'+@SQLCmd+'" queryout "'+@FilePathCmd+'" -c -t, -T -S' + @@servername
   print(@ExportCmd)
   exec master..xp_cmdshell @ExportCmd

Answer 1

Here with the Cursor statement that I had created to execute each row SQL Command in the Table

use TestDB

DECLARE @storedprocedure_name varchar(100)
DECLARE @cmd VARCHAR(4000)

DECLARE @FilePath varchar(100)
DECLARE @FileNames varchar(100)

DECLARE spCursor CURSOR FAST_FORWARD FOR 
SELECT CommandText,FilePath,FileNames FROM dbo.MultipleQuery
OPEN spCursor
FETCH NEXT FROM spCursor INTO @storedprocedure_name ,@FilePath,@FileNames
WHILE @@fetch_status = 0
BEGIN

 SET @cmd = 'bcp "' +
  @storedprocedure_name + '" queryout "' + @FilePath +@FileNames +'" -c -UTF8 -T -S'+@@SERVERNAME
PRINT @cmd


EXEC master..xp_cmdshell @cmd

 FETCH NEXT FROM spCursor INTO @storedprocedure_name,@FilePath,@FileNames
END
CLOSE spCursor
DEALLOCATE spCursor