Dynamic conditions in WHERE clause

Question

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?

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

Answer 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