Executing a WHERE clause conditionally in SQL

Question

I have an application in a SQL Server 2008 database. This database stores a procedure that queries one of the tables. This stored procedure takes two parameters: userName and ID

The userName parameter will always be passed. However, the identifier field will be either NULL or a valid value. If the value is something other than NULL, I need to consider it in the WHERE clause of my query. Unfortunately, I'm not sure how to do this. I'm currently trying

SELECT * FROM TaskTicket t WHERE t.[UserName]=@userName AND -- This is where I am stumped 

Thank you for your help!

Answer 1

 SELECT * FROM TaskTicket t WHERE t.[UserName]=@userName AND (@ID IS NULL OR t.[ID] = @ID) 

Answer 2

Try the following:

 SELECT * FROM TaskTicket t WHERE t.[UserName]=@userName AND (@ID is null or -- replace this comment with your logic ) 

Answer 3

Group legend together

 select * from TaskTicket t Where t.[UserName]=@userName AND ((t.Id is null and (conditions_when_id_is_null)) or (t.Id is not null and (conditions_when_id_is_not_null))) 

Answer 4

 SELECT <column list> FROM TaskTicket T WHERE T.[UserName] = @username AND (T.id = @id OR @id IS NULL) 

Remember that in some cases this may cause a suboptimal query plan. This is probably not very important in this case, if your table is not huge, and you do not have an index in UserName and ID.

Answer 5

We hope that it’s more efficient than using the OR condition:

 SELECT * FROM TaskTicket t WHERE t.[UserName]=@userName AND t.[ID] LIKE COALESCE(@ID,'%') 

NB: will only work if the identifier is a non-NULLable field. (You can use CAST and COALESCE on t. [ID] otherwise, but then it is unlikely to be more effective than the OR condition.)

Alternatively, use dynamic SQL in your stored procedure to completely omit the condition t. [ID] if @ID is NULL.

Answer 6

 declare @SQL nvarchar(max) declare @WHERE_ID nvarchar(20) set @WHERE_ID = ( CASE WHEN @ID is null THEN '' ELSE ' AND ID = ' + CAST(@ID as nvarchar(10)) END ) set @SQL = 'SELECT * FROM TaskTicket WHERE UserName = ' + @userName + @WHERE_ID EXEC @SQL 

Answer 7

 Create procedure Procedure1 ( @Param1 nvarchar(100)=null, ) AS BEGIN SELECT ColumnName1,ColumneName2 FROM TableName WHERE (@Param1 IS NULL OR ColumnName1=@Param1) END