如何将先前的结果设置为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###.

如果用户名和密码在users表中匹配，我想生成access token。但是我如何使用先前返回的值来执行下一个插入查询呢?我用### #标记了缺失值，我不知道##。

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.

请帮助我。谢谢,提前。

3 个解决方案

#1

Simply pass the @userId as your second parameter for INSERT

只需将@userId作为插入的第二个参数传递给它

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

#2

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

#3

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

#1