sql query stored in a column. How to execute this?

Experts - I have a table called "RunQuery" which has ReportID nvarchar(max) and Formula nvarchar(Max)

The data for this table gets generated from another stored procedure which provides an output like this;

ReportID    Formula
123             Select Name as Customer, sum(val) as Sum_Val from Table1
123             Select Org as Customer, Sum(Val) as Sum_Val from Table1
123             Select Prod as Customer, Sum(met) as Sum_Val from Table1
& so on.

Here, ReportID will be same for all rows however the content in formula will vary. it will have same headers if run seperately.

I need to run these individual queries & store the data in a output table.

Ex:
Insert into Table_Output (Customer, Sum_Val)
@Formula

I need this like a loop. can you help please?
LVL 7
ManjuIT - Project ManagerAsked:
Who is Participating?
 
ste5anSenior DeveloperCommented:
E.g.

DECLARE @RunQuery TABLE ( ReportID INT, Formula VARCHAR(MAX) );

INSERT INTO @RunQuery
VALUES	( 123, 'SELECT Name AS Customer, SUM(val) AS Sum_Val FROM Table1' ),
	( 123, 'SELECT Org AS Customer, SUM(Val) AS Sum_Val FROM Table1' ),
	( 123, 'SELECT Prod AS Customer, SUM(met) AS Sum_Val FROM Table1' );

DECLARE @Query AS VARCHAR(MAX) = '';

SELECT	@Query += CHAR(13) + CHAR(10) + 'UNION ALL ' + RQ.Formula
FROM	@RunQuery RQ
WHERE	RQ.ReportID = 123;

SET @Query = STUFF(@Query, 1, 12, '');
SET @Query = 'INSERT INTO Table_Output (Customer, Sum_Val) ' + CHAR(13) + CHAR(10) + @Query;

EXECUTE ( @Query );

Open in new window

0
 
ManjuIT - Project ManagerAuthor Commented:
Perfect ;) Thanks alot Stephan
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.