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

Solution 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.

Solution 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.

Solution 3

Its best practice to have TOP 1 1 always.

What if I use SELECT 1 -> If condition matches more than one record then your query will fetch all the columns records and returns 1.

What if I use SELECT TOP 1 1 -> If condition matches more than one record also, it will just fetch the existence of any row (with a self 1-valued column) and returns 1.

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

Solution 4

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 

Solution 5

You can define a variable @Result to fill your data in it

DECLARE @Result AS INT

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

View more solutions

Comments

[Doug Knudsen]

Using "SELECT *" vs "SELECT 1" or "SELECT 0" with EXISTS has no cost difference in SQL Server. More a matter of preference.

[Bill Tür stands with Ukraine]

This is absolutely not necessary - see this question

[Lakshmanan Dhamotharan]

@BillTür I still believe this is valid Query. Its all based on the WHERE condition. If your condition exactly matches one record then no issues. Suppose your condition matches the more number of records then it will cost. It took Compute scalar cost: 16% when I have select 1. Compute Scalar cost is: 0% when I use SELECT TOP 1 1 Hence, it makes difference based on the number of records matches