SQL Server IF EXISTS THEN 1 ELSE 2

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

488448 次浏览

小开

最佳答案

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.

小开

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

小开

What the output that you need, select or print or .. so on.

so use the following code:

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

小开

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

小开

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.

小开

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