Condition in sql query

Question

Hi

I want to insert in sql query something like that:

Select * from Users where id=[if @userId>3 then @userId else "donnt use this condition"] and Name=[switch @userId  
case 1:"Alex"
case 2:"John"
default:"donnt use this condition"];

How can i do it?

yet another similar question

When showAll is false it works ok but when showAll is true it returns nothing. Why and how to make it working right? IsClosed column has a bit type.

Select * from orders where IsClosed=CASE WHEN @showAll='false' THEN 'false' ELSE NULL END;

Answer 1

Select *
from Users
where id = CASE WHEN @userId>3 THEN @userId ELSE NULL END
OR name = CASE WHEN @userId = 1 THEN 'Alex' WHEN @UserId = 2 THEN 'John' ELSE NULL END

Answer 2

This will perform horribly:

Select * 
  from Users 
 where (@userid > 3 AND id = @userId)
    OR (@userId BETWEEN 1 AND 2 AND name = CASE 
                                             WHEN @userId = 1 THEN 'Alex' 
                                             ELSE 'John' 
                                           END)

The best performing option is dynamic SQL:

SQL Server 2005+

DECLARE @SQL NVARCHAR(4000)
    SET @SQL = 'SELECT u.*
                  FROM USERS u
                 WHERE 1 = 1 '

    SET@SQL = @SQL + CASE 
                       WHEN @userId > 3 THEN ' AND u.id = @userId '
                       ELSE ''
                     END

    SET@SQL = @SQL + CASE @userId
                       WHEN 1 THEN ' AND u.name = ''Alex'' '
                       WHEN 2 THEN ' AND u.name = ''John'' '
                       ELSE ''
                     END

BEGIN

 EXEC sp_executesql @SQL, N'@userId INT', @userId

END

For more info on SQL Server's dynamic SQL support, read "The Curse and Blessings of Dynamic SQL"

Answer 3

Please try this:

select * 
from Users 
where id = (case when @userId > 3 then @userId 
                else id end)
and Name = (case cast(@userId as varchar)
                when '1' then 'Alex'
                when '2' then 'John'
                else Name end)

Or I think this will perform better:

select aa.*
from (select *
            , case when @userId > 3 then @userId 
                    else id end as UserID
            , case cast(@userId as varchar)
                    when '1' then 'Alex'
                    when '2' then 'John'
                    else Name end as UserName
        from Users) aa
where aa.id = aa.UserID
    and aa.Name = aa.UserName

You might want to define each field that you only need on your select, instead of using asterisk(*).