CODEWITHSUNDEEP
sql-server
Eaton
Eaton

Reputation: 1320

Setting output parameters in SELECT statement with an IF EXISTS check

I am trying to make an efficient SQL stored procedure for retrieving user data from my database, but I am running into a syntax issue I can't seem to figure out.

Basically, I want to assign my output variable within my SELECT statement. I also want to see if the user actually exists by IF EXISTS. Unfortunately, I can't seem to do both.

Here is my procedure:

CREATE PROCEDURE [dbo].FindUser(@UserID binary(16), @UserExists bit OUTPUT, @Name 

nvarchar(MAX) OUTPUT)
AS
    SET NOCOUNT ON
    IF EXISTS (SELECT @Name = Name FROM Users WHERE UserID = @UserID)
    BEGIN
        SET @UserExists = 1
    END
RETURN

Currently, it gives me an "SQL46010 :: Incorrect syntax near @Name." error. If I remove IF EXISTS, the statement compiles fine!

Why does the IF EXISTS check cause a syntax error?

Upvotes: 0

Views: 1324

Answers (2)

Mikael Eriksson
Mikael Eriksson

Reputation: 138970

set @UserExists = 0;

select @Name = Name, 
       @UserExists = 1
from Users
where UserID = @UserID;

Upvotes: 4

M.Ali
M.Ali

Reputation: 69524

SET NOCOUNT ON
IF EXISTS (SELECT 1 FROM Users WHERE UserID = @UserID)
BEGIN
    SET @UserExists = 1
    /* do other stuff here select user name or whatever */
END

If there is a record for @UserID in users table Selecting 1 will return true for exists clause and control will enter the BEGIN..END block.

Upvotes: 0

Related Questions