如何将多个选择查询值分配给存储过程中的声明变量

How to assign multiple select query values into declared variables in a stored procedure?

如何将多个选择查询值分配给存储过程中的声明变量?

This works

SELECT @id = USER_ID 
FROM USERS 
WHERE EMAIL=@EMAIL

but I need something like this

但我需要这样的东西

SELECT @id = USER_ID, @corp = CORPORATE_ID 
FROM USERS 
WHERE EMAIL = @EMAIL

above query doesn't work (which means I cannot assign multiple values), is there any way to make possible this thing?

以上查询不起作用(这意味着我无法分配多个值),有没有办法让这个东西成为可能?

Thanks in advance....

提前致谢....

1 个解决方案

#1

I don't know what you're doing - but selecting multiple values and assigning them to multiple variables in T-SQL works just fine - inside or outside of a stored procedure.

我不知道你在做什么 - 但选择多个值并将它们分配给T-SQL中的多个变量就可以正常工作 - 在存储过程的内部或外部。

If you have the AdventureWorks sample database - try this:

如果您有AdventureWorks示例数据库 - 请尝试以下操作:

CREATE PROCEDURE dbo.GetAddressTypeDetails (@AddressTypeID INT, @Name VARCHAR(50) OUTPUT, @Modified DATETIME OUTPUT)
AS BEGIN
    SELECT
        @Name = Name, @Modified = ModifiedDate
    FROM
        [Person].[AddressType]
    WHERE
        [AddressTypeID] = @AddressTypeId
END

and then execute this stored procedure like this:

然后像这样执行这个存储过程:

DECLARE @AddressTypeID INT
DECLARE @Name VARCHAR(50) 
DECLARE @Modified DATETIME

-- TODO: Set parameter values here.
SET @AddressTypeID = 5

EXECUTE [dbo].[GetAddressTypeDetails] @AddressTypeID, @Name OUTPUT, @Modified OUTPUT

SELECT 
    @Name, @Modified

and you'll get the output of

你会得到的输出

Shipping   2002-06-01 00:00:00.000

No problems here.....

这里没问题.....

#1