Execute SQL commands stored in database column - output of column should be in csv

Question

Similar to this post
https://learn.microsoft.com/en-us/answers/questions/547423/execute-sql-commands-stored-in-database-column.html

I was trying to get the 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 number,
name varchar(1000),
sal int,
DOB datetime,
OrgID number
)

Create table Table2
(
OrgID number,
Orgname varchar(1000)
)

Create table Table3
(
dummy number
)

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
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

As they are no records in table3, it should not output in final result.

Answer 1

Hi @AbdulWahab Khan
Please check this:

SELECT @sqlcmd = STUFF(( SELECT ';'+char(10)+ 'SELECT '''+Command+''' AS Command,'+RIGHT(Command,LEN(Command)-6) +char(10)   
                         FROM #List order by [OrderBy] FOR XML PATH('') ), 1, 1, '')  
--PRINT @sqlcmd  
EXEC(@sqlcmd);  

Note that this code worked based on the premise that every command starts with 'SELECT'.

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.