SQL Server IF EXISTS THEN 1 ELSE 2

Question

Using Sql Server 2012. I have a stored procedure and part of it checks if a username is in a table. If it is, return a 1, if not, return a 2. This is my code:

IF EXISTS (SELECT * FROM tblGLUserAccess WHERE GLUserName ='xxxxxxxx') 1 else 2

However, I keep receiving the below error:

Incorrect syntax near '1'.

Is this even possible with an IF EXIST?

Regards,

Michael

Answer 1

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.

Answer 2

How about using IIF?

SELECT IIF (EXISTS (SELECT 1 FROM tblGLUserAccess WHERE GLUserName ='xxxxxxxx'), 1, 2)

Also, if using EXISTS to check the the existence of rows, don't use *, just use 1. I believe it has the least cost.

Answer 3

In SQL without SELECT you cannot result anything. Instead of IF-ELSE block I prefer to use CASE statement for this

SELECT CASE
         WHEN EXISTS (SELECT 1
                      FROM   tblGLUserAccess
                      WHERE  GLUserName = 'xxxxxxxx') THEN 1
         ELSE 2
       END