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

AbdulWahab Khan 41 Reputation points

Similar to this post

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))
('SELECT * FROM Table1'),
('SELECT * FROM Table2'),
('SELECT * FROM Table3')


SELECT @sqlcmd = STUFF(( SELECT ';'+char(10)+ Command +char(10) FROM #List order by [OrderBy] FOR XML PATH('') ), 1, 1, '')


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.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,629 questions
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,547 questions
{count} votes

1 answer

Sort by: Most helpful
  1. CosmogHong-MSFT 22,616 Reputation points Microsoft Vendor

    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  

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

    Best regards,

    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.