Thank you with the solution Adam .

I made a lil improvement to accomodate %searchword% for the LIKE using Northwind db ,might be useful to others out there.

-----------

CREATE PROCEDURE dbo.searchfilterbycolumn2

(

@categorytext...

I planning to check only 2 or 3 columns with the same data type-varchar(150) , and from a table join to another table . the result set would return 7-8...

I am new to this , so can you please elaborate how to execute it, I notice you were using system stored proc .

I run all the dynamic - sql...

is that mean I cant do it using generic user-defined stored procedure ?

my app would really be easier if I can stick to use the normal stored proc ......

Thank you very much.

I finally know where I got it wrong . The simplified query you provided, the logic the way I wanted to, is right , thus the problem...