CODEWITHSUNDEEP
sqlsql-serversql-server-2008
AK_
AK_

Reputation: 8099

SQL Server Dynamic Condition for Where

Assuming I have two tables:

The Column Conditions.Condition contains a SQL Condition for example "std.Age >2" or "std.Class = 3"

I want somthing that does the following:

SELECT std.Id as StudentId, con.Id as ConId
FROM   Students as std,
       Condition as con
WHERE  con.Condition

Maybe a function that takes Student Id?

How do I achieve this?

Students:

1 , Yossi, 25, 3..
2 , David, 22, 3..
3 , Jhon, 5, 2..
4 , Smith, 25, 4..

Conditions:

1 , Age > 3
2 , Class = 4
3 , Name LIKE '%i%'

result would be (Condition, Studnet):

(1,1) (1,2) (1,3) (1,4) // all are older than 3
(2,4)                   // only Smith is in class 4
(3,1) (3,2) (3,4)       // all except jhon have an i in their name

Upvotes: 1

Views: 752

Answers (2)

Hiren Dhaduk
Hiren Dhaduk

Reputation: 2780

Try following Query : 

DECLARE @CONDITIONS varchar(max) =
REPLACE(REPLACE((
SELECT'UNION ALL SELECT ID AS STUDENTID , ' + CONVERT(VARCHAR , ID ) + ' AS CONDITIONID FROM STUDENTS WHERE ' + CONDITION + ' ' 
FROM CONDITION
FOR XML PATH('')
) , '&LT;' , '<') , '&GT;' , '>') 

SET @CONDITIONS = (SELECT SUBSTRING(@CONDITIONS , 11 , LEN(@CONDITIONS)))

EXEC(@CONDITIONS)

This is more generalize way to apply condition . if you want to apply only one condition then add where clause in query from where we fetch all the condition . you can run this query for one or more condition . currently i have written this query which apply all the conditions .

sqlfiddle

Upvotes: 2

Curt
Curt

Reputation: 5722

You'll have to build a string, which includes condition, and then EXECUTE it. This is what is called "dynamic SQL"

As an example:

 DECLARE @sql Varchar(400)

 SELECT @sql = 'SELECT * FROM MyTable WHERE ' + condition
  FROM Conditions
 WHERE cond_key = 1

 -- This will yield you a full SQL statement, if the condition record
 -- is there.

 EXECUTE(@sql)            -- And this will execute it

Upvotes: 0

Related Questions