February 16, 2017 at 3:53 pm
Hello,
Can a where clause be inserted into a select statement as a variable?
For example, can I do this:
DECLARE @WhereClause nvarchar(max)
SET @WhereClause = 'WHERE (col1 like \'%abc%\' AND col2 like \'%xyz%\') OR (col3 like \'%abc%\' AND col4 like \'%xyz%\')'
SELECT * FROM MyTable @WhereClause
The reason I want to do this is that we want to send a set of search criteria from our application to a stored procedure. The application will parse the search criteria into a SQL WHERE clause and then pass that as a parameter to the stored procedure. In the stored procedure, I want to simply plop the parameter into the SELECT query.
Can this be done, or are there any alternatives that don't require drastically more work?
February 16, 2017 at 3:58 pm
You would need to use dynamic SQL, and you should never "simply plop" anything that will be executed into a query. You should always take steps to prevent SQL injection.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
February 17, 2017 at 6:24 am
If you're going to do this, use sp_executesql and build out the query and WHERE clause using parameters. As was already mentioned, otherwise you are absolutely opening yourself to SQL Injection attacks.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply