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

AbdulWahab Khan 41 Reputation points
2022-09-08T06:45:16.397+00:00

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.

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
Transact-SQL
Transact-SQL
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
    2022-09-08T08:55:58.127+00:00

    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.