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.