Use LIKE in a stored procedure with an input parameter

Question

I have 2 input parameters, and I want to search for users where the o part is the username @Username , and the part of the @Name name is that this user part of the @Username and the part of the @Name name

 SELECT * FROM tbl_answer WHERE an_del = 0 AND u_username = ISNULL(LIKE %@Username%, u_username) OR u_name = ISNULL(LIKE @Name, u_name) 

How can I use LIKE in this stored procedure?

Answer 1

 CREATE PROC dbo.SearchAnswers @Username nvarchar(20), @Name nvarchar(20) AS SELECT * FROM tbl_answer WHERE an_del=0 and u_username LIKE '%' + ISNULL(@Username, u_username) + '%' OR u_name LIKE ISNULL(@Name, u_name) 

Example for SQLServer2008

Answer 2

You need to use dynamic sql, for example sql server:

 create procedure MyProc ( @Username varchar(30), @Name varchar(30) ) as begin exec ('SELECT * from tbl_answer where an_del=0 and u_username=isnull(like ''%' +@Username +'%'',u_username) or u_name=isnull(like ''' +@Name +''',u_name)') end