Change stored procedure based on input parameters

Question

I'm writing an 'advanced search' page for a web application. It basically has a form that goes:

Search for query in the forum forum name posted by username in the last date

The idea is that users can leave fields blank if they wish and the search won't include it.

I'd rather not write a stored procedure for searching based on every possible combination of leaving values blank. Is there a way I can write a stored procedure so that the search changes based on which parameters passed in are blank?

Something like (pseudo code)

SELECT * FROM Table WHERE Message = @query

(if @username isn't null)
AND Username = @Username

(if @forum isn't null)
AND Forum = @forum

..and so on.

Thanks for any help you can provide!

I think it should help: http://stackoverflow.com/questions/532468/ignoring-a-null-parameter-in-t-sql — Iwo Kucharski, Jul 04 '12 at 22:13
[Dynamic Search conditions in T-SQL](http://www.sommarskog.se/dyn-search.html) - Erland Sommarskog's done all the hard work to work out what works best. — Damien_The_Unbeliever, Jul 05 '12 at 06:21
Thank you very much for all the advice - I'll try it tonight and see how it goes. — shauneba, Jul 05 '12 at 08:08

score 2 · Accepted Answer · answered Jul 04 '12 at 22:29

2

This should work:

SELECT *
FROM YourTable
WHERE   (Message = @query OR @query IS NULL)
AND     (Forum = @forum OR @forum IS NULL)
AND     (Username = @username OR @username IS NULL)
AND     (LastDate = @lastdate OR @lastdate IS NULL)

answered Jul 04 '12 at 22:29

Lamak

69,480
12
108
116

This approach can cause a performance hit, expecially if there are a lot of optional parameters. See Iwo Kucharski's comment above for a more robust solution. – DeanOC Jul 04 '12 at 22:42

score 1 · Answer 2 · edited Oct 25 '12 at 10:03

  CREATE Proc [dbo].[sp_sarch_with_filter]                   
  @param1 varchar(50)=NULL,                                
  @param2 varchar(50)=NULL,                                
  @param3 varchar(50)=NULL,                                

  AS                                  
  Begin                                                                  

  Declare @dynamicsql varchar(max)                  



  Set @dynamicsql='select  * from tabelName where 1=1'                               


  if(@param1 is not null and @param1  <> '')                  
  Set @dynamicsql=@dynamicsql+' and col1 like''%'+@param1 +'%'''                  

  if(@param2 is not null and @param2  <> '')                  
  Set @dynamicsql=@dynamicsql+' and col2 like''%'+@param2 +'%'''                  

  if(@param3 is not null and @param3  <> '')                  
  Set @dynamicsql=@dynamicsql+' and col3 like''%'+@param3 +'%'''                  


  Set @dynamicsql=@dynamicsql+' Order by col4 Desc'

  Print @dynamicsql                  

  Execute(@dynamicsql)

This is not recommended, as it is vulnerable to SQL injection attacks. https://www.owasp.org/index.php/Top_10-2017_A1-Injection — Stamp, Mar 08 '18 at 15:46
"like" queries, just like any other query, are very much inject-able when not sanitized. In your specific example, see https://stackoverflow.com/a/15579926/416314 for an example injection attack on a "like" statement — Stamp, Oct 19 '18 at 14:33

Change stored procedure based on input parameters

2 Answers2