Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 How to pass Items from a table as sql where ...

Author  Topic 

micnie_2020
Posting Yak Master

232 Posts

Posted - 2013-06-14 : 11:38:00
Hi All,

I have a table contain:

ItemIndex Item
0 email
1 LIKE
2 mic

3 job_title
4 LIKE
5 IT
6 age
7 =
8 45


Notes: (3 set per each filter (0-3, 4-6, 7-9, 10-12 ...)

How can i manipulate above item(s) to be as bold section as below where .... without hardcoded?

select * from members
where email like '%mic%' and job_title like 'IT' and age=45

Please advise.

Thank you.

Regards,
Micheale

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-06-14 : 12:53:02
Using Pivot but would need to be utilized via dynamic sql

Declare @query varchar(8000)
set @query='select * from members where '
declare @tab table (ItemIndex int,Item varchar(10))
insert into @tab values (0,'email'),(1,'LIKE'),(2,'mic'),(3,'job_title'),(4,'LIKE'),(5,'IT'),(6,'age'),(7,'='),(8,'45')
SET @query=@query+
(SELECT [0]+' '+[1]+' ''%'+[2]+'%'' AND '+[3]+' '+[4]+' ''%'+[5]+'%'' AND '+[6]+' '+[7]+' '+
FROM (
SELECT
PK as 'Item',[0],[1],[2],[3],[4],[5],[6],[7],
FROM (
select 'Item' as Pk,ItemIndex,Item from @tab
) Src
Pivot
(
Max(Item) For ItemIndex in ([0],[1],[2],[3],[4],[5],[6],[7],)
)AS Pvt
)Query
)
Print @query

Cheers
MIK
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-14 : 15:16:48
so is it always wildcard search for 1st, string for second and integer for third in each of the groups?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
   

- Advertisement -