Pass WHERE clause as parameter into a SQL Server stored procedure

how to pass where condition as parameter in stored procedure
execute stored procedure sql server with input parameters
dynamic where clause in sql stored procedure
execute stored procedure with multiple parameters
how to execute stored procedure in select statement sql server
advanced stored procedure examples in sql server
how to pass query as parameter to stored procedure in sql server
how to call stored procedure in view sql server

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

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).

How to pass Where Clause as a parameter to the stored procedure , Hide Expand Copy Code. @AccountNumber As Varchar(50), @FirstName As Varchar(50), @LastName As Varchar(50), @ActiveClinicID As  I am passing where condition to the stored procedure as a parameter This is executing fine. Could you please suggest me, How to create "DynamicWhereClause" stored procedure without EXEC in stored procedure? I need to Execute ("DynamicWhereClause" stored procedure without EXEC ) using above two examples. Please guild me. Thanks in advance.

As mentioned in the comments there's usually never a good reason for passing in a complete clause: - You're removing abstraction of the Database layer (if you need to switch DBMSs, you'll need to change the where clause syntax, assuming it's different). - You might be opening yourself up to injection attacks.

Understand your requirement better and parameterize your stored procedure accordingly. For example, you need the following queries possible:

select * from TABLE where column1 like '%XX%' and column2 like '%YY%'
select * from TABLE where column1 like '%XX%' or column2 like '%YY%'`

pass 3 parameters column1Value, column2Value, clauseConnector

Then use conditionals to execute different queries:

IF @clauseConnector = 'AND'
  BEGIN
    select * from TABLE where c1 like '%' + @column1Value  + '%' AND c2 like '%' + @column2Value  + '%'
  END
ELSE
  BEGIN
    select * from TABLE where c1 like '%' + @column1Value  + '%' OR c2 like '%' + @column2Value  + '%'
  END

You can also use dynamic sql to build your query in one statement.

SET @query = 'SELECT * FROM TABLE WHERE ' + 
'Column1 like ''%' + column1Value  + '%'' '
+ @clauseConnector  + ' ' +
IIF (@column2Value IS NOT NULL,'Column2 like ''%' + column2Value  + '%'''
EXECUTE(@SQLQuery)

How to pass where condition as parameter of stored procedure , In the Sp you then would have to use dynamic sql and build you sql statement and execute it. It is not very pretty and opens up the possibility of  However, if you want to assign your passed parameters to specific variable inside SQL Server, you will have to mention that parameter as mentioned in the second method. Method 2: In this method when we execute the stored procedure, we also include the variable to which we want to assign parameter.

This sounds simple enough based on your edit there's a varchar(MAX) column which you want to search. Let's call that col1

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

An Essential Guide to SQL Server Stored Procedure Parameters, Second, we used @min_list_price parameter in the WHERE clause of the To execute the uspFindProducts stored procedure, you pass an argument to it as  Here Mudassar Ahmed Khan explained how to pass comma separated (delimited) values as Parameter to Stored Procedure in SQL Server. The comma separated (delimited) values will be split into Table rows and will be used for querying inside the SQL Server Stored Procedure.

How to pass parameters in WHERE clause from another stored , dynamic where clause in sql stored procedure sql server in clause parameter to stored procedure execute stored procedure sql server with input parameters In SQL 2008 and up pass them as a table-valued parameter. For earlier versions pass them separated by the unique character that you know should never be in the field and then split by that character into a table and use that table. This article should explain everything about this problem: Arrays & Lists in SQL Server

Using Parameters for SQL Server Queries and Stored Procedures, https://social.msdn.microsoft.com/Forums/sqlserver/en-US/6a72e826-bb45- I would like the first stored procedure to display only the results of  Parameterizing a SQL IN clause? SQL Server - In clause with a declared variable. Hi, I am facing problem passing parameters to 'IN' clause. I am using the below query. Query: SELECT Topics.Topic_Id FROM Topics Where Topic_Description IN (''+ @Topics +'') This query works when the parameter has single value.

SQL Stored Procedures for SQL Server, Using Parameters for SQL Server Queries and Stored Procedures In this tip we look at different ways to pass in values as parameters to queries Generally, when creating a condition in a query where you might use one of  Pass the in statement to a Sql SP via a variable and concatenate it into a query in the SQL and execute using sp_execute sql. create procedure myproc(@clause varchar(100)) as begin exec sp_executesql 'select * from users where userid in ( ' + @clause +' )' end. share.

Comments
  • If you have to do it that way then dynamic SQL is your only option. However thats not a good way to do it - leave you open to injection, and harms performance.
  • But don't. This is a terrible idea. You are creating implicit links between the 2 layers that only complicate your architecture.
  • That's one of the main reasons I don't want to use dynamic SQL. Any other options?
  • You'd need to explain what you are trying to accomplish in more detail in order to come up with a better approach.
  • Side note: you should not use the sp_ prefix for your stored procedures. Microsoft has reserved that prefix for its own use (see Naming Stored Procedures), and you do run the risk of a name clash sometime in the future. It's also bad for your stored procedure performance. It's best to just simply avoid sp_ and use something else as a prefix - or no prefix at all!
  • I thought of using a User defined table type, but the query entered by the user is something like (A OR B) AND (C or D). This filter needs to be applied on the column. Using the table type, it will be tricky to generate this dynamic SQL. Also, this is the only field that is needing dynamic SQL. I have 50 other parameters passed to the procedure and converting all to dynamic SQL is a pain
  • You can use your existing search query into a temp table and then use the dynamic SQL for this final filter.
  • Its going to be dynamic SQL either way, its just how you pass the details into your SP - and I wouldn't want to be passing in SQL fragments.
  • Gotcha. Will try it out.
  • If you see the linked question, you will understand that this approach is not going to work.
  • This wouldn't work like that as we have multi columns values to be checked through.
  • The implementation is possible without a dynamic query but you will need carefully consider the consequences. If you have one varchar(max) column called col1 and this column has a copy of the data from that row as a string then it’s possible to simplify you search criteria to "where col1 like @param". This avoids dynamic queries but exposes other issues. If the size of the other fields are fixed add them up and use that instead of max. You could use a trigger update the col1 maybe with an XML row. Will work for less than 5,000 row. Otherwise use sp_executesql