Dynamic SQL where condition with values from another table

Question

I want to build a dynamic SQL query where I can use data from another table as where condition. Let's assume I have two tables: one table with financial data and the other one with conditions. They look something like this:

Table sales

c006    mesocomp    c048      c020   c021
-----  ---------- -------    -----   ----
120     01TA       MICROSOFT  2      239

and a condition table with the following data:

   dimension   operator    wert_db 
   ---------   --------    ------- 
   sales.c006     <           700 
   sales.c048   not like    'MIC%'
   sales.c021   in         (203,206)

I want to select all data from sales with the conditions stated in the condition table. So I have an SQL Query as follows:

SELECT * 
FROM sales 
WHERE sales.c006 < 700 
  AND sales.c048 NOT LIKE 'MIC%' 
  AND sales.c021 IN (203, 206)

You either need a massive `case` expression or dynamic SQL. Storing SQL code in columns is not generally considered a good practice, unless you have a really good reason. — Gordon Linoff, Mar 15 '19 at 14:30
Yeah you are right that it is not a good practice but our customer delivers an excel sheet containing the conditions which are transfered directly into tables... — Rick Grimes, Mar 15 '19 at 14:32

score 2 · Answer 1 · answered Mar 15 '19 at 14:40

Since you've posted no attempt to solve or research this yourself, I'll point you in a direction to get you started.

Your question already mentions using Dynamic SQL, so I assume you know at least what that is. You're going to populate a string variable, starting with 'SELECT * FROM Sales '.

You can use the STUFF...FOR XML PATH technique to assemble the conditions rows into a WHERE clause.

One change to the linked example is that you'll need to concatenate dimension, operator and wert_db into one artificial column in the innermost SELECT. Also instead of separating with a comma, you'll separate with ' AND '. And change the parameters of the STUFF function to take off the length of ' AND ' instead of the length of a comma.

score 1 · Answer 2 · answered Mar 15 '19 at 14:45

DECLARE @tblSales TABLE
(
    c006     VARCHAR(10),
    mesocomp VARCHAR(100),
    c048     VARCHAR(100),
    c020     VARCHAR(100),
    c021     VARCHAR(100)
)

INSERT INTO @tblSales(c006, mesocomp, c048, c020, c021)
VALUES(120,'01Ta','Microsoft','2','239')

SELECT * FROM @tblSales

DECLARE @tblCondition TABLE
(
    Id INT,
    dimension VARCHAR(100),
    operator  VARCHAR(10),
    wert_db   VARCHAR(100)
)

INSERT INTO @tblCondition(Id, dimension, operator, wert_db) VALUES(1,'sales.c006','<','700')
INSERT INTO @tblCondition(Id, dimension, operator, wert_db) VALUES(1,'sales.c048','not like','''MIC%''')
INSERT INTO @tblCondition(Id, dimension, operator, wert_db) VALUES(1,'sales.c021','in','(203,206)')

DECLARE @whereCondition VARCHAR(400)    
SELECT @whereCondition = COALESCE(@whereCondition + ' ', '') + dimension + ' ' + operator + ' ' + wert_db + ' AND '
FROM @tblCondition
SET @whereCondition = SUBSTRING(@whereCondition,0, LEN(@whereCondition) - 3) 

PRINT @whereCondition

DECLARE @sql VARCHAR(4000)
SET @sql = 'SELECT * FROM @tblSales Where ' + @whereCondition

PRINT @sql
EXEC(@sql)
--please use real tables so you will get everything working.

Dynamic SQL where condition with values from another table

2 Answers2