Dynamic conditions in WHERE clause

问题:

I have a stored procedure and would like to know if its possible to build up a dynamic where condition based on a parameter.

Lets say I have this query:

SELECT *
FROM tbl_Users

Now, I have a parameter called @username, which I would like to use to build up a dynamic where condition (which through my program might be 1 or more conditions). To achieve something like that I use the following statement:

SELECT *
FROM tbl_Users
@username -- where this parameter might hold a condition string such as "Where usr_Username = 5 and usr_first_name like '%Frank%' etc

Is it possible to do something like this?

回答1:

You're going to have to break into dynamic sql for this.

it would run something like this:

declare @sql varchar(max)

set @sql = '
    SELECT *
    FROM tbl_Users
    WHERE ' + @username

exec (@sql)

回答2:

I'm not certain I understand you, but if my understanding is correct, you can do the following (NOTICE: injection vulnerable)

DECLARE @SQL varchar(500) = 'SELECT * FROM tbl_users ' + @username

EXEC @SQL

回答3:

From what I know, this is not going to work. You're going to need to generate the script you want to execute and use the exec command.

回答4:

You are really not supposed to be concatenating SQL keywords and parameters into one single string as shown in some of the responses above for reasons of opening the doors to SQL injection (One of the contributors actually called it out. That's a wise Warning!).

Instead, you are supposed to parameterize your SQL and execute the system SP sp_executesql.

A very good code example is shown in this StackOverflow posting.