Can I use EXISTS keyword in sql query for assignment of result

Question

Can I assign a value with EXISTS in the query. Currently it is giving me error Incorrect syntax near '='. But when I uses this query only it runs as expected.

IF EXISTS (SELECT @PK_LOGIN_ID=PK_LOGIN_ID FROM dbo.M_LOGIN 
    WHERE LOGIN_NAME=@LOGIN_NAME AND PASSWORD=@PASSWORD AND FK_ROLE_ID=@FK_ROLE_ID)
            RETURN @PK_LOGIN_ID
    ELSE
            RETURN 0

Answer 1

No, you can't. You need to use the code you have posted.

Answer 2

You can not assign a variable in an EXISTS clause.
One option is to split the assignment and the check up or remove the assignment alltogether.

SQL Statement

IF EXISTS (
    SELECT  PK_LOGIN_ID 
    FROM    dbo.M_LOGIN 
    WHERE   LOGIN_NAME=@LOGIN_NAME   
            AND PASSWORD=@PASSWORD 
            AND FK_ROLE_ID=@FK_ROLE_ID)
  SELECT  @PK_LOGIN_ID = PK_LOGIN_ID 
  FROM    dbo.M_LOGIN 
  WHERE   LOGIN_NAME=@LOGIN_NAME   
          AND PASSWORD=@PASSWORD 
          AND FK_ROLE_ID=@FK_ROLE_ID
  ELSE
    SET @PK_LOGIN_ID = 0

RETURN @PK_LOGIN_ID

Answer 3

You can't use exists as you are retrieving data (@PK_LOGIN_ID) so you will need to select it.

SET @PK_LOGIN_ID = (
    SELECT PK_LOGIN_ID FROM dbo.M_LOGIN 
    WHERE LOGIN_NAME=@LOGIN_NAME AND PASSWORD=@PASSWORD AND FK_ROLE_ID=@FK_ROLE_ID
    )

RETURN ISNULL(@PK_LOGIN_ID, 0)

(This assumes 1 or 0 matching rows only)

Answer 4

RETURN  COALESCE(
        (
        SELECT  TOP 1
                PK_LOGIN_ID
        FROM    dbo.M_LOGIN 
        WHERE   LOGIN_NAME = @LOGIN_NAME
                AND PASSWORD = @PASSWORD
                AND FK_ROLE_ID = @FK_ROLE_ID
        ), 0)