Executing a WHERE clause conditionally in SQL

Question

Hello,

I have an application on a SQL Server 2008 database. This database has a stored 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 ID field will either be NULL or an actual 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 positive how to do this. Currently, I'm 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

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

Just be aware that this may cause a non-optimal query plan in some cases. That's probably not a big deal in this case unless your table is huge and you don't have an index on UserName and ID.

Answer 3

Try this:

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

Answer 4

Group the conditionals 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 5

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

Answer 6

Hopefully more efficient than using an OR condition:

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

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

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

Answer 7

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