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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply