Unanswered: inserting where clause into select query as variable
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?
Only if you use Dynamic SQL, which carries a bunch of problems along with it (danger of SQL injection, performance/recompilation problems, hard to read/support). Basically, you build up the where clause in a string, then execute the string:
declare @sql varchar(2000)
set @sql = 'select * from sys.objects'
set @sql = @sql + ' where type = 'U'
The quick answer is, "yes, with dynamic SQL." But the right answer is not program this way. This is what is sometimes referred to as "automobiles, squids and Lady Gaga coding'; it means we have no idea what it is doing until runtime and it can do anything. Look at the term "SQL injection" for some of the problems.
If you can take a course on basic software engineering (I hope they still teach it!) And look up the idea of cohesion and modules of code. This is a measure of how well a module of code (in any language) does a single task, with one entry and one exit point. The highest form of cohesion is called functional cohesion, and the usual example is a mathematical function. No matter where you invoke it, a cosine is a cosine is a cosine; it always does the same task. The worst kind of cohesion is dependent on where the module is invoked and external flags are awfull; this is called flag cohesion.
You will want to control the parameters going in. You can do this with case expressions and constraints. But it means that you have to know what you are after.