Execute a predefined tSql query stored in a column of table

Today’s post is inspired from the question asked here in the stackoverflow site. So the question is how to execute a predefined tsql query which is stored in a column of table and then display the result in one report. You can find the answer here i gave him. You might find it yourself in this type of situation in your application where you need to display the consolidated report over your other report in which you need to display the name of the report and its associated count or some other data.

Allright, let’s create this situation. For explanation i copied the data from that question. so here is the required table with some required records in it.


CREATE TABLE #tmpConsReport
(
	ReportName [varchar](100), 
	ReportQuery [varchar](2000)
);

INSERT INTO #tmpConsReport (ReportName, ReportQuery) 
	VALUES ('Sales Daily Report Count', 'Select COUNT(*) FROM Sales WHERE SaleDate = CAST(GETDATE() AS DATE)'),
			    ('Sales Weekly Report Count', 'Select COUNT(*) FROM Sales WHERE SaleDate >= CAST(GETDATE() -6 AS DATE) '),
				('HR Dept. Count', 'Select COUNT(*) FROM Users DepartmentID = 1'),
				('Todays New Joiny', 'Select TOP 1 firstname FROM Users ORDER BY EmpID DESC');

Now to execute the query which is stored in column of table we need to build dynamic query which looks something like this –


DECLARE @query VARCHAR(MAX) = ''

SELECT @query += 'SELECT ' + QUOTENAME(ReportName, '''') 
							+ ' AS ReportName, CAST((' + ReportQuery + ') AS VARCHAR) AS Result UNION ALL ' 
From #tmpConsReport

SET @query = LEFT(@query, LEN(@query) - 10)

PRINT(@query)
EXEC(@query)

If you don’t get any idea after seeing this query, no problem. Let’s print that dynamic query and it looks like this –


SELECT 'Sales Daily Report Count' AS ReportName, 
			CAST((Select COUNT(*) FROM Sales WHERE SaleDate = CAST(GETDATE() AS DATE)) AS VARCHAR) AS Result 

UNION ALL 

SELECT 'Sales Weekly Report Count' AS ReportName, 
			CAST((Select COUNT(*) FROM Sales WHERE SaleDate >= CAST(GETDATE() -6 AS DATE) ) AS VARCHAR) AS Result 

UNION ALL 

SELECT 'HR Dept. Count' AS ReportName, 
			CAST((Select COUNT(*) FROM Users DepartmentID = 1) AS VARCHAR) AS Result 
			
UNION ALL 

SELECT 'Todays New Joiny' AS ReportName, 
         CAST((Select TOP 1 firstname FROM Users ORDER BY EmpID DESC) AS VARCHAR) AS Result

Hmmm Now looks better. But its easy right!!! Yes, using UNION ALL i have built the dynamic query and then execute it.

That’s it guys. Hope you like this post and learned something new today. Have a nice day.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s