SQL output of dynamic queries should be in csv

Question

Referring to the code in one of the links found in the internet - that executes sql command stored in DB column

I was trying to get similar output in the form of csv - like query and data along with headers in one result. Like if we have this data in sql server 2014

CREATE TABLE Table1 (empid INT,
                     name VARCHAR(1000),
                     sal INT,
                     DOB DATETIME,
                     OrgID INT);

CREATE TABLE Table2 (OrgID INT,
                     Orgname VARCHAR(1000));

CREATE TABLE Table3 (dummy INT);


INSERT INTO Table1
VALUES (100, 'tst', '10000', '1990-11-20', 1);
INSERT INTO Table1
VALUES (101, 'tst', '10000', '1990-11-20', 1);
INSERT INTO Table1
VALUES (102, 'tst', '10000', '1990-11-20', 2);

INSERT INTO Table2
VALUES (1, 'org1');
INSERT INTO Table2
VALUES (2, 'org2');


CREATE TABLE #List (Command VARCHAR(MAX),
                    OrderBy INT IDENTITY(1, 1));
INSERT INTO #List
VALUES ('SELECT * FROM Table1'),
       ('SELECT * FROM Table2'),
       ('SELECT * FROM Table3');

DECLARE @sqlcmd VARCHAR(MAX);

SELECT @sqlcmd = STUFF(
                 (SELECT ';' + CHAR(10) + Command + CHAR(10)FROM #List ORDER BY [OrderBy] FOR XML PATH('')), 1, 1, '');

EXEC (@sqlcmd);

Now when i execute the sql's in #List, the output should result with comma seperated. As they are no records in table3, it should not output in final result. Am expected the the result all in one column seperated by comma - screenshot attached.

OUTPUT  
SELECT FROM Table1, empid,name,sal,DOB,OrgID
SELECT FROM Table1, 100,tst,10000,1990-11-20 00:00:00.000,1
SELECT FROM Table1, 101,tst,10000,1990-11-20 00:00:00.000,1
SELECT FROM Table1, 102,tst,10000,1990-11-20 00:00:00.000,2
select from Table2,OrgID, Orgname
select from Table2,1,org1
select * from Table2,2,org2

SQL output of dynamic queries should be in csv

0 Answers