Have Geek Brain

Can I use EXISTS keyword in SQL query to assign result

Can I assign a value with EXISTS

in the request. It is currently giving me an Invalid syntax error near = = . But when I use this query, it only works 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
+2


a source to share


5 answers 


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)


+2


a source


No, you cannot. You need to use the code you posted.



+1


a source


You cannot assign a variable in an EXISTS clause.
One option is to split the assignment and check 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
+1


a source 


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
+1


a source


You cannot use it when fetching 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 only 1 or 0 matching lines)

0


a source






More articles:


All Articles