CODEWITHSUNDEEP
sqlsql-serverwhere-clause
Himgauri K
Himgauri K

Reputation: 11

Where Clause from Another table

So the goal is to make a SQL query for table A but the where clause is stored in table B.

here is example

table B:

BatchNumber | DataItem | Operator | TValue 
12345       | Branch   | =        | 2700
12345       | StockType| =        |S
12345       | PRCode   | <>       |AD

from this table I need to make below where statement

select *
from testdata.TableA
where Branch = 2700 and StockType = 'S' and PRCode <> 'AD'

here are the different methods I tried using to form a sql

declare @whereClause varchar(400)
declare @schema varchar(10) = 'testdata'
declare @Batch varchar(15) = 12345

select @whereClause = N' COALESCE(@whereClause + ' ' ,'') +  trim(DataItem) + ' ' + trim(Operator) + ' ' + 
        case 
            when trim(TValue) LIKE '[A-Za-z]%' then '''' + trim(TValue) + '''' + ' AND' 
            when trim(TValue) = ' ' then  ''''  + '''' + ' AND' 
            else trim(TValue) + ' AND'
        end 
from' + @schema + '.TableB where BatchNumber =' +  @Batch  

--remove AND from the end of statement 
SET @whereClause = SUBSTRING(@whereClause,0, LEN(@whereClause) - 3) 

PRINT @whereClause

However this is giving me syntax error with single codes in the case statement.

Msg 102, Level 15, State 1, Line 4
Incorrect syntax near ' ,') + trim(DataItem) + '.

I need case statement to add single quote around string operators.

I also tried different method with FOR XML PATH(''), but with this single quotes are eliminated and "<>" signs are changed to "gtlt"

really appreciate any help.

Upvotes: 1

Views: 272

Answers (1)

zedfoxus
zedfoxus

Reputation: 37059

It seems like some of your single-quotations may have to be quoted or you could use variables. For example: @whereClause = N' COALESCE(@whereClause + ' ' ,'')... may require @whereClause = N' COALESCE(@whereClause + '' '' ,'''')... or something on those lines. You could store there result of COALESCE(@whereClause + ' ' ,'') in a variable and then concat it with other variables in a similar way.

Alternatively, you could write a query in SQL Server like this:

select 'select * '
union all select 'from testdata.TableA '
union all select 'where 1 = 1 '
union all
select concat(
  '   and ',
  dataitem, ' ', operator, ' ',
  case
  when tvalue like '%[0-9]%' then tvalue
  else concat('''', tvalue, '''')
  end
)
from test /* or tableB */
where batchnumber = 12345; /*optional where clause*/

You'll get the output like this:

select * 
from testdata.TableA 
where 1 = 1 
   and Branch = 2700
   and StockType = 'S'
   and PRCode <> 'AD'

Example:

https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=12c3110a0d82e754bfc16f686779cddd

Upvotes: 2

Related Questions