SQL Server If Exists Then 1 Else 2

SQL Server IF EXISTS THEN 1 ELSE 2

If you want to do it this way then this is the syntax you're after;

IF EXISTS (SELECT * FROM tblGLUserAccess WHERE GLUserName ='xxxxxxxx') 
BEGIN
   SELECT 1 
END
ELSE
BEGIN
    SELECT 2
END

You don't strictly need the BEGIN..END statements but it's probably best to get into that habit from the beginning.

SQL Server: IF EXISTS ; ELSE

EDIT

I want to add the reason that your IF statement seems to not work. When you do an EXISTS on an aggregate, it's always going to be true. It returns a value even if the ID doesn't exist. Sure, it's NULL, but its returning it. Instead, do this:

if exists(select 1 from table where id = 4)

and you'll get to the ELSE portion of your IF statement.

---

Now, here's a better, set-based solution:

update b
  set code = isnull(a.value, 123)
from #b b
left join (select id, max(value) from #a group by id) a
  on b.id = a.id
where
  b.id = yourid

This has the benefit of being able to run on the entire table rather than individual ids.

SQL if exists then Yes' else No' in a new column

You would normally do this using exists:

select t1.*,
       (case when exists (select 1
                          from table2 t2
                          where t2.student = t1.student and t2.subject = 'math'
                         )
             then 'yes' else 'no'
        end) as has_math
from table1 t1;

Unlike Tim's answer, this is guaranteed to return only one row per student, even if there are multiple 'math' rows in the second table.

How do I perform an IF...THEN in an SQL SELECT?

The CASE statement is the closest to IF in SQL and is supported on all versions of SQL Server.

SELECT CAST(
             CASE
                  WHEN Obsolete = 'N' or InStock = 'Y'
                     THEN 1
                  ELSE 0
             END AS bit) as Saleable, *
FROM Product

You only need to use the CAST operator if you want the result as a Boolean value. If you are happy with an int, this works:

SELECT CASE
            WHEN Obsolete = 'N' or InStock = 'Y'
               THEN 1
               ELSE 0
       END as Saleable, *
FROM Product

CASE statements can be embedded in other CASE statements and even included in aggregates.

SQL Server Denali (SQL Server 2012) adds the IIF statement which is also available in access (pointed out by Martin Smith):

SELECT IIF(Obsolete = 'N' or InStock = 'Y', 1, 0) as Saleable, * FROM Product

Return same select if exists

You can use Merge Concatenation to do this. Just join the two queries with UNION ALL, add an ordering column to ORDER BY, then SELECT TOP (1) WITH TIES

SELECT TOP (1) WITH TIES`
  *
FROM (
    SELECT *, 1 AS Ordering
    FROM [MyTable] t
    WHERE Property = 1

    UNION ALL

    SELECT *, 2
    FROM [MyTable]
    WHERE Property = (
        SELECT Property
        FROM [OtherTable]
        where OtherProperty = 1)
) t
ORDER BY Ordering;

If Property in the second query is guaranteed to be >= 1 then you can just do it all in one and ORDER BY Property

SELECT TOP (1) WITH TIES`
  *
FROM [MyTable] t
WHERE Property IN (
 1,
 (
     SELECT Property
     FROM [OtherTable]
     where OtherProperty = 1
  )
ORDER BY Property;

I suggest you have an index on Property with relevant INCLUDEs to support this query

IF EXISTS (SELECT 1...) vs IF EXITS (SELECT TOP 1 1...)

If you view the execution plan for these queries you can see that they are identical. Good coding practices would ask that you leave out the "TOP 1" but they should run identical either way.

Return true (1), if a joined data entry exists, else false (0) in MS-SQL

You can use the following, using CASE WHEN instead of ISNULL:

SELECT Base.*, A.id, B.id, 
  CAST(CASE WHEN A.id IS NULL THEN 0 ELSE 1 END AS BIT) AS IsA,
  CAST(CASE WHEN B.id IS NULL THEN 0 ELSE 1 END AS BIT) AS IsB
FROM Base LEFT JOIN A ON Base.id = A.base_id
  LEFT JOIN B ON Base.id = B.base_id

demo on dbfiddle.uk

This solution, compared to your current approach, has the same efficiency. But also see this answer (check multiple columns for NULL values). There you can see the ISNULL solution is less efficient. In your case it makes no big difference.

Also be careful: The ISNULL can also return 0 in case the column values is 0. So with your current approach you would get False in such a case.

---

Related Topics