ISZERO

• ASFSDJG

  Mr or Mrs. 500

  Points: 500

  More actions

  October 4, 2011 at 4:08 am

  #248885

  I have a query,

  SELECT

  *

  FROM User

  where User_id = @User_id or @User_id = 0

  I just want to compare the parameter @User_id with User.user_id only if the parameter is not zero.

  I can do with the above query

  Is there any other simple way to do this?

• spaghettidba

  SSC Guru

  Points: 105732

  More actions

  October 4, 2011 at 6:12 am

  #1390083

  I suggest avoiding this kind of expression. It can easily bring performance issues as it's prone to bad parameter sniffing.

  Use dynamic SQL instead:

  DECLARE @sql nvarchar(max)

  SET @sql = '

  SELECT *

  FROM User

  WHERE ' + CASE @User_id WHEN 0 THEN ' 1 = 1 ' ELSE ' User_id = @User_id ' END

  EXEC sp_executesql @sql, N'@User_id int', @User_id

  Hope this helps

  Gianluca

  -- Gianluca Sartori