主页 / dba / 问题 / 99210
Accepted
user3323654
Asked: 2015-04-30 21:11:17 +0800 CST

如何将查询结果存储到变量中？SQL Server 2008

  • 772

我有两个表，我想将存储过程的结果查询存储到一个变量中。

UserTable：

UserID int primary key identity
CredentialsID int
AccountType nvarchar(15)
ModifiedDate date

CredentialsTable：

Credentials ID int primary key identity
Password

存储过程：

Create procedure addFaculty 
    @ID int 
as
begin
    IF NOT EXISTS (Select AccountType from UserTable where AccountType = 'Faculty')
    begin 
        Insert into CredentialsTable values ('iamafaculty');

        Select CredentialsTable.CredentialsID 
        from CredentialsTable 
        where CredentialsTable.Password = 'iamafaculty';
    end
end

执行 select 语句将返回一个我想存储在@ID参数中的凭据 ID。所以我可以在我的其他陈述中使用它。

sql-server stored-procedures

3 个回答

  1. Best Answer

    如果要设置 ID 变量参数，则需要将其指定为 OUTPUT 参数，并在查询中设置它的值：

    Create procedure addFaculty 
@ID int OUTPUT
as
begin
    IF NOT EXISTS (Select AccountType from UserTable where AccountType = 'Faculty')
    begin 
        Insert into CredentialsTable values ('iamafaculty');

        Select @ID = CredentialsTable.CredentialsID 
        from CredentialsTable 
        where CredentialsTable.Password = 'iamafaculty';
    end
end

    这就是你要问的吗？

    • 2

  2. 为此感谢@sabin bio

    使用Scope_Identity()返回当前会话中任何表中的最后一个标识。的范围Scope_Identity()仅限于当前范围，即最后一个插入语句的位置。

    https://msdn.microsoft.com/en-us/library/ms190315.aspx

    • 1

  3. 在您存储的过程中声明参数，如下所示

     Create procedure addFaculty 
(
@ID int OUTPUT
)
as
begin
Insert into CredentialsTable values ('iamafaculty');

        Select @ID = CredentialsTable.CredentialsID 
        from CredentialsTable 
        where CredentialsTable.Password = 'iamafaculty';

end

    然后最后在调用时，检索 id 值，如下所示

    declare @id1 int

exec addfaculty
@id=@id1 out
    select @id
    • 0

相关问题