SQL Set变量用于选择结果

I was wondering if it is possible to set a declared variable to a return value from a select result? Something like:

我想知道是否可以将声明的变量设置为选择结果的返回值？就像是：

@WatchedSeconds
SET @WatchedSeconds = 200
DECLARE @SelectedVideo int
SET @SelectedVideo = (SELECT TOP 1 * FROM Video v WHERE v.VideoID = 12)

IF @SelectedVideo IS NOT NULL
   BEGIN
      IF @SelectedVideo.VideoLength = @WatchedSeconds
        BEGIN
           --DO SOMETHING
        END
      IF @SelectedVideo.SomeOtherColumn = @SomethingElse
        BEGIN
        END
   END

It's for using some information from the SELECT result multiple places in a Stored Procedure. I know that I can set a variable to e.g, a integer, and set it to the selected result, if it returns a integer, e.g:

它是用于将SELECT结果中的一些信息用于存储过程中的多个位置。我知道我可以将变量设置为例如一个整数，并将其设置为所选结果，如果它返回一个整数，例如：

DECLARE @VideoSeconds int
SET @VideoSeconds = (SELECT v.Length FROM Video v WHERE v.VideoID = @VideoID)

This way I have to make multiple variables, and multiple SELECT calls if I need to use more values from the Video result. And that's what I want to avoid.

这样我就必须创建多个变量，并且如果我需要使用Video结果中的更多值，则需要多次SELECT调用。这就是我想要避免的。

4 个解决方案

#1

You can try something like

你可以尝试类似的东西

(declare variables first...)

（首先声明变量......）

SELECT TOP 1 @var1=col1, @var2=col2, @var3=col3, [...] FROM YourTable WHERE YourFilter

EDIT: All together this seems not to be the best approach... With SQL you should not think in values and single rows but rather in result sets (set based programming). Your thinking leads to many tiny selects, while loops, cursors and all this stuff one should avoid.

编辑：所有这一切似乎不是最好的方法...使用SQL你不应该考虑值和单行而是结果集（基于集合的编程）。你的想法导致许多微小的选择，而循环，游标和所有这些东西应该避免。

#2

You can do this simply by running:

您只需运行以下命令即可：

SELECT @videoSeconds = v.Length FROM Video v WHERE v.VideoID = @VideoID

so as to not add the SET part.

以免添加SET部分。

Also, you must make sure that only 1 row is being returned by the query, otherwise it will generate an error.

此外，您必须确保查询仅返回1行，否则将生成错误。

#3

You can store the results in a temporary table or table variable:

您可以将结果存储在临时表或表变量中：

SELECT TOP 1 *
INTO #SelectedVideo
FROM Video v
WHERE v.VideoID = 12;

Then you can assign values from the table later in your code. Something like:

然后，您可以稍后在代码中分配表中的值。就像是：

IF ( (SELECT VideoLength FROM #SelectedVideo) = @WatchedSeconds)

However, for your particular example, if you have an index on video(VideoId), then there is little to be gained performance-wise from using a temporary table.

但是，对于您的特定示例，如果您有视频索引（VideoId），那么使用临时表在性能方面几乎无法获得。

#4

If what you're trying to get is similar to returning a dataset in a procedural language (so you can type something like Result.Field1 = 'Test') then I don't think this is possible. You'll just need to declare multiple variables and make the SELECT call as

如果您想要获得的类似于以过程语言返回数据集（因此您可以键入类似Result.Field1 ='Test'的内容），那么我认为这不可行。您只需要声明多个变量并将SELECT调用作为

SELECT TOP 1 @var1=col1, @var2=col2, @var3=col3, [...] FROM YourTable WHERE YourFilter

as @Shnugo suggests

正如@Shnugo所说

The 'dataset' equivalent structure in SQL is cursors, but they require variables to be set up as well, so there's no benefit there.

SQL中的“数据集”等效结构是游标，但它们也需要设置变量，因此没有任何好处。

#1