Pass WHERE clause as parameter into a SQL Server stored procedure

Question

I am working on a stored procedure where one of the parameters used in WHERE clause needs to be sent from the code. For ex -

CREATE PROCEDURE sp_test
    @param1 
AS
BEGIN
    SELECT * 
    FROM Table 
    WHERE @param1
END

In the above stored procedure, the value passed to @param1 would be something like Col1 LIKE '%abc%' AND col1 LIKE '%xyz%' I understand that this can be done using dynamic SQL, but I would prefer not to use this.

Any help would be appreciated.

EDIT

I have a varchar(MAX) column and I am looking for searching this column based on values entered by the user. The operator used (AND / OR) is selected by the user on the front end. This is inline to the question asked here

Answer 1

I would create a user defined table type with 3 columns:

• match type (equals, like etc)
  
• searched value
  
• and/or

I would pass this into my SP and then use dynamic SQL to build a query.

You don't really have many other options when allowing the user to define the search criteria. But I would definitely pass the search conditions in in a structured format so that you have ultimate control over the final SQL (as opposed to the app poking pre-formed SQL in).