CODEWITHSUNDEEP
sql-servert-sql
Rajdip Patel
Rajdip Patel

Reputation: 551

How to user previous resultset into next query in SQL Server

I want to generate access token if username and password will match in users table. But how can I use previous returned value to next insert query? I have marked missing value with ###I don't know###.

CREATE PROCEDURE [dbo].[GetAccessToken]
    @userId varchar(50),
    @password varchar(50)
AS
   SELECT Id 
   FROM Users 
   WHERE UserId = @userId AND Password = @password 

   IF @@ROWCOUNT != 0 
   BEGIN
       SET @token = NEWID();

       INSERT INTO AccessTokens (Token, UserId) 
       VALUES(CONVERT(varchar(255), @token), ###I Don't Know###)
        END
       RETURN 0
   END

Please help me. Thanks, in advance.

Upvotes: 2

Views: 189

Answers (3)

Oleksandr Fedorenko
Oleksandr Fedorenko

Reputation: 16904

Use option with INSERT .. SELECT .. pattern

CREATE PROCEDURE [dbo].[GetAccessToken]
@userId varchar(50),
@password varchar(50)
AS
BEGIN
  INSERT INTO AccessTokens (Token, UserId)
  SELECT CONVERT(varchar(255), NEWID()), Id
  FROM Users 
  WHERE UserId = @userId AND Password = @password
END

Upvotes: 1

Praveen Nambiar
Praveen Nambiar

Reputation: 4892

Simply pass the @userId as your second parameter for INSERT

INSERT INTO AccessTokens (Token, UserId) 
VALUES(CONVERT(varchar(255), @token), @userId)

Upvotes: 2

Andrey Gurinov
Andrey Gurinov

Reputation: 2885

Try this:

CREATE PROCEDURE [dbo].[GetAccessToken]
    @userId varchar(50),
    @password varchar(50)
AS
   DECLARE @ID INT
   SELECT @ID = Id 
   FROM Users 
   WHERE UserId = @userId AND Password = @password 

   IF (@ID IS NOT NULL )
   BEGIN
       SET @token = NEWID();

       INSERT INTO AccessTokens (Token, UserId) 
       VALUES(CONVERT(varchar(255), @token), @ID)

       RETURN 0
   END

Upvotes: 1

Related Questions