如何在存储过程中存储值

I have a stored procedure that looks like this:

我有一个如下所示的存储过程：

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE Guests_Load
    @Id AS Varchar(30)  
AS
    SET NOCOUNT ON

    SELECT *
    FROM Guests
    WHERE HotelId = @Id

    SELECT 
        GroupId,
        HotelName,
    FROM 
        HotelView(NOLOCK)   
    WHERE 
        HotelId = @Id
GO

Now, I want to create a new result set by writing another SELECT statement.

现在，我想通过编写另一个SELECT语句来创建一个新的结果集。

However, I want the groupId that is returned from the second SELECT. How do I do this? I tried:

但是，我想要从第二个SELECT返回的groupId。我该怎么做呢？我试过了：

DECLARE @hotelId int;

SELECT 
    @hotelId = GroupId,
    HotelName,
FROM 
    HotelView(NOLOCK)   
WHERE 
    HotelId = @Id

but I get an error saying

但我得到一个错误说

A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations

为变量赋值的SELECT语句不能与数据检索操作结合使用

1 个解决方案

#1

A simple solution is just to run the query twice:

一个简单的解决方案是运行查询两次：

CREATE PROCEDURE Guests_Load (
    @Id AS Varchar(30) 
) AS
BEGIN

    SET NOCOUNT ON;

    SELECT *
    FROM Guests
    WHERE HotelId = @Id;

    SELECT GroupId, HotelName
    FROM HotelView 
    WHERE HotelId = @Id;

    DECLARE @GroupId int;

    SELECT @GroupId = GroupId
    FROM HotelView
    WHERE HotelId = @Id;

END;

Personally, I am not a fan of stored procedures printing out tables. So, I would be inclined to have all the SELECTs use only variables. But if you want the print-out, then one method is to run the code twice.

就个人而言，我不是打印出表格的存储过程的粉丝。所以，我倾向于让所有SELECT只使用变量。但是如果你想要打印输出，那么一种方法是运行代码两次。