CODEWITHSUNDEEP
sql-servert-sqlsql-server-2012
isabasan
isabasan

Reputation: 448

Executing Queries Stored in a Column of a Table

I need some help about executing queries stored in a column of a table.

My table name is Stats. It includes some reporting queries like this:

id     | key     | query
------ | ------  | ------
1      | reportA | select 1
2      | reportB | select 2
3      | reportC | select count(id) from Users
4      | reportD | select top 1 firstname from Users order by 1 desc
Values in query column are just samples. They will be more complex queries in production but they always return one result.

Is it possible to write a query with an output like this:

key     | result |
------  | ------ |
reportA | 1      |
reportB | 2      |
reportC | 500    |
reportD | Jack   |

Upvotes: 1

Views: 140

Answers (3)

Krishnraj Rana
Krishnraj Rana

Reputation: 6656

You can try it using dynamic query something like this - 

DECLARE @temp table ([key] [varchar](100), [query] [varchar](1000));

INSERT @temp ([key], [query]) VALUES ('reportA', 'select 1');
INSERT @temp ([key], [query]) VALUES ('reportC', 'select count(id) from Users');
INSERT @temp ([key], [query]) VALUES ('reportA', 'select top 1 firstname from Users order by 1 desc');

DECLARE @qry varchar(max) = ''

Select @qry += 'SELECT ' + QUOTENAME([key], '''') 
            + ' AS Yourkey, CAST((' + [query] + ') AS VARCHAR) AS Result UNION ALL ' 
From @temp

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

PRINT(@qry)
EXEC(@qry)

Result

Yourkey Result
-------------------
reportA   1
reportC   500

Upvotes: 2

Anton
Anton

Reputation: 2882

Use While loop to go through each ID, get "query" for each iteration, assign it to nvarchar(max) variable, then create temp table #temp (value varchar(max)), then run "INSERT INTO #temp EXEC (@query), then get the value from #temp and finally update your table.

Note that you may need to update your table first to include proper conversions to VARCHAR data type.

Upvotes: 0

TheGameiswar
TheGameiswar

Reputation: 28920

yes use CASE..

Select 
case 
when key='reporta' then replace(query,'select','')
when key='reportb' then replace(query,'select','')
when key='reportc' then cast((select count(id) from users) as varchar(100))
when key='reportd' then (select top 1 firstname from Users order by 1 desc)
End
from yourtable

Upvotes: 0

Related Questions