SQL select using filters saved in a table

Question

I have a table in which filters to be used in a select statement.
The table looks like this:

How can I use this column values in a select statement?
The table where I am using select statement cannot be joined with this filter table.

Answer 1

This calls for dynamic SQL (e.g. you construct your query as a string and then execute using sp_executeSQL system procedure). It is not a very common design and may lead to various errors and problems. Why do you need such implementation?

Answer 2

Hi NaomiNNN
Thanks for your reply.
I need to create some user defined custom filters.
I managed to write a query which outputs a select statement.
I don't know how to use this in a sp_executeSQL.
Can you help me?

Answer 3

declare @alenzi nvarchar(max);

set @alenzi = 'select * from MyTable where 1=1 ' + (select top (1) filter_str from dbo.Filters where ...);

print @alenzi ; -- verify then comment out

begin try
execute sp_executeSQL @alenzi ;
end try
begin catch
-- raiserror here or just throw
end catch

Answer 4

If you store filters like this, it is not really a surprise that you end up with syntax error, because there many be syntax errors in the stored data, or may not fit where you try to use it in the query.

In this case, there is an obvious error in the generated SQL:

where  1= 1 
 +  
 ( select filterstr from [tblFilterLikeJira] where filtername='Modules')

That plus sign does not belong there. And nor do I think that the subquery is what you intended. You wanted to have the filter here, didn't you?

Dynamic SQL is an advanced feature, and if you have never worked with dynamic SQL before, you will have to face a learning curve as you debug the generated SQL. You will need to have patience.

Generally, before you start building dynamic SQL, first get a clear idea of what the final query should look like. Then you can strive to generate it.

I have an article on my web site The Curse and Blessings of Dynamic SQL, where you can learn about some of the pitfalls with dynamic SQL.

Answer 5

Hi NaomiMNN
Thank you for your answer.
My query is as follows:

declare @sql nvarchar(max);  
--set @sql= 'select * from MyTable where 1=1 ' + (select top (1) filter_str from dbo.Filters where ...);  
  
set @sql = '   
select   count(*) Amount,     
sum(         
[D) Unlikely] * twdu.[WDC]         
+ [C) Low]* twdl.[WDC]       
+[B) Medium]* twdm.[WDC]        
+  [A) High]* twdh.[WDC]        
)  WDC , getdate() CalculatedDate  
from       
(       
select  
[RiskSeverity]     [RiskSeverityName]  
, [RiskSeverity]     
,   [RiskSeverity]  [RiskSeverityQty]       
, [RiskProbability]     
, [RiskProbability]     [RiskProbabilityName]   
, IssueNo      
, pkey  
FROM [SoftWareQuality].[dbo].[tblSWQuality] swq       
where  1= 1   
+ ( select filterstr from [tblFilterLikeJira] where filtername=''Modules'')  
)  
as s PIVOT (       
count([RiskSeverityQty]) FOR [RiskProbability] IN ( [A) High],[B) Medium],[C) Low],[D) Unlikely]        
)        
)       
AS p         
left join tblwdc twdu on twdu.[RiskSeverity]         
= ltrim(rtrim( TRIM(''0123456789'' FROM p.[RiskSeverity] ) ))        
and twdu.RiskProbability = ''Unlikely''      
left join tblwdc twdl on twdl.[RiskSeverity]         
= ltrim(rtrim( TRIM(''0123456789'' FROM p.[RiskSeverity] ) ))       
and twdl.RiskProbability = ''Low''       
left join tblwdc twdm on twdm.[RiskSeverity]        
= ltrim(rtrim( TRIM(''0123456789'' FROM p.[RiskSeverity] ) ))        
and twdm.RiskProbability = ''Medium''      
left join tblwdc twdh on twdh.[RiskSeverity]         
= ltrim(rtrim( TRIM(''0123456789'' FROM p.[RiskSeverity] ) ))        
and twdh.RiskProbability = ''High''     '  
  
print @sql; -- verify then comment out  
  
--begin try  
--execute sp_executeSQL @sql;  
--end try  
--begin catch  
---- raiserror here or just throw  
--end catch  

And the output print is:

select   count(*) Amount,     
sum(         
[D) Unlikely] * twdu.[WDC]         
+ [C) Low]* twdl.[WDC]       
+[B) Medium]* twdm.[WDC]        
+  [A) High]* twdh.[WDC]        
)  WDC , getdate() CalculatedDate  
from       
(       
select  
[RiskSeverity]     [RiskSeverityName]  
, [RiskSeverity]     
,   [RiskSeverity]  [RiskSeverityQty]       
, [RiskProbability]     
, [RiskProbability]     [RiskProbabilityName]   
, IssueNo      
, pkey  
FROM [SoftWareQuality].[dbo].[tblSWQuality] swq       
where  1= 1   
+    
( select filterstr from [tblFilterLikeJira] where filtername='Modules')  
)  
as s PIVOT (       
count([RiskSeverityQty]) FOR [RiskProbability] IN ( [A) High],[B) Medium],[C) Low],[D) Unlikely]        
)        
)       
AS p         
left join tblwdc twdu on twdu.[RiskSeverity]         
= ltrim(rtrim( TRIM('0123456789' FROM p.[RiskSeverity] ) ))        
and twdu.RiskProbability = 'Unlikely'      
left join tblwdc twdl on twdl.[RiskSeverity]         
= ltrim(rtrim( TRIM('0123456789' FROM p.[RiskSeverity] ) ))       
and twdl.RiskProbability = 'Low'       
left join tblwdc twdm on twdm.[RiskSeverity]        
= ltrim(rtrim( TRIM('0123456789' FROM p.[RiskSeverity] ) ))        
and twdm.RiskProbability = 'Medium'      
left join tblwdc twdh on twdh.[RiskSeverity]         
= ltrim(rtrim( TRIM('0123456789' FROM p.[RiskSeverity] ) ))        
and twdh.RiskProbability = 'High'   

I copied the above text and ran the query which resulted i n the following error: