1

I'm using SQL Sever 2008. I have several WHERE statements stored in the database. I am trying to build a query using the query criteria in the table.

So let's say I had a table called 'Categories' 

+----------+------------------------+  
| Seq | SQLWHERE          
+----------+------------------------+  
|        0 | ([ID] >= 60010   
|        1 | And                      
|        2 | [ID] <= 60020)   
|        3 | Or                       
|        4 | [ID] = 60025     
|        5 |  Or                      
|        6 | [ID] = 60035)   
+----------+------------------------+

So in essence i would need to build a query that used the SQLWHERE lines above in order of sequence 0-6.

So I'd need something to the effect below, but this does not work.

SELECT *
FROM AnotherTable
WHERE (SELECT SQLWHERE FROM Categories ORDER BY Seq)

because I would want to SQL query to render

SELECT *
FROM AnotherTable
WHERE  
([ID] >= 60010   
And                      
[ID] <= 60020)   
Or                       
[ID] = 60025     
Or                      
[ID] = 60035)
Juan Carlos Oropeza
  • 47,252
  • 12
  • 78
  • 118
trice
  • 48
  • 7

2 Answers2

0

First convert every row in a string Concatenate many rows into a single text string?

and pass it to sp_executesql

Community
  • 1
  • 1
Juan Carlos Oropeza
  • 47,252
  • 12
  • 78
  • 118
0

You are going to have to create a function or stored procedure to query the where items and build the SQL dynamically. Then execute the SQL with your attached where clauses. Here is a stored proc example. Change "AnotherTable" to your table name and change CategoryX to Category.

CREATE PROCEDURE ExecutDynamicWhere
AS
BEGIN

    DECLARE @Where VARCHAR(8000)  
    DECLARE @sql VARCHAR(8000)  

    SELECT @Where = COALESCE(@Where + ' ', '') + SQLWhere From CategoryX

    Set @sql = 'SELECT * FROM AnotherTable WHERE ' + @Where

    EXECUTE sp_executesql @sql

END
GO