Use 2 SP at the same time?

Question

bcp "EXEC [databasname].[storedprocedure]" queryout C:\test\bcp_outputTable.csv -c -T -SPC01 -r\n

Is it possible to use two stored procedure at the same time in the code above? THe first SP contains the name of the column and the second is content for the header.

Answer 1

Create a Temp table with varchar/nvarchar columns, INSERT your two Stored Procedure results into the temp table (first stored procedure is your column headers). 

/*
-- Reference: http://stackoverflow.com/questions/5292069/union-the-results-of-multiple-stored-procedures
*/
CREATE TABLE #foo (bar int ...)

INSERT #foo
exec MyStoredProcedure 1

INSERT #foo
exec MyStoredProcedure 2

INSERT #foo
exec MyStoredProcedure 3

...

---

web: www.ronnierahman.com

Answer 2

Nope that is not going to work out; I just made a quick test, but I only got the first result set.

While this can be done with BCP, it becomes quite a kludge, particularly if you cannot change the procedure that produces the data. Maybe it's time to go the SSIS route? (He said, he who does not know SSIS himself, oh well.)

---

Erland Sommarskog, SQL Server MVP, [e-mail]