CODEWITHSUNDEEP
asp.netsqlsql-server
Billy
Billy

Reputation:

Search by parameter only if user enters a value

I want to rewrite this query so that if @UserName gets passed in a null value then it leaves the Client_User out of the search criteria. I only want it to search by name if the user enters a name in the username textbox on the webform. I'm not sure how to do this.

select * from weblogs.dbo.vwlogs 
where Log_time between @BeginDate and @EndDAte 
and  client_user=@UserName

Upvotes: 0

Views: 194

Answers (4)

Taylor Gerring
Taylor Gerring

Reputation: 1835

The best solution is to utilize sp_execute_sql. For example:

--BEGIN SQL
declare @sql nvarchar(4000)

set @sql = 
'select * from weblogs.dbo.vwlogs 
where Log_time between @BeginDate and @EndDate'
+ case when @UserName is null then '' else 'and client_user = @UserName' end

sp_execute_sql
@sql
, @params = '@UserName varchar(50)'
, @UserName = @UserName
--END SQL

As muerte mentioned, this will have a performance benefit. According to BOL:

sp_executesql can be used instead of stored procedures to execute a Transact-SQL statement a number of times when the change in parameter values to the statement is the only variation. Because the Transact-SQL statement itself remains constant and only the parameter values change, the Microsoft® SQL Server™ query optimizer is likely to reuse the execution plan it generates for the first execution.

Upvotes: 0

markom
markom

Reputation: 2040

The solution from Kristen will work, but if you need performance, don't do it because the plan will be cached only for the first condition.

So, if your procedure gets called with the NULL parameter first, that query will be cached.

If you need higher performance, use an IF statement and create two distinct queries.

In more complicated queries event sp_execsql will be faster.

Upvotes: 0

Carlton Jenke
Carlton Jenke

Reputation: 3025

select * 
from weblogs.dbo.vwlogs 
where Log_time between @BeginDate and @EndDAte 
  and (client_user=@UserName or @UserName IS null)

Upvotes: 0

Kristen
Kristen

Reputation: 4301

select * from weblogs.dbo.vwlogs where Log_time between @BeginDate and @EndDAte and (@UserName IS NULL OR client_user=@UserName)

Upvotes: 5

Related Questions