如何将select列存储在变量中?

how do i store the select column in a variable?

如何将select列存储在变量中?

try this but throw me an error Incorrect syntax

尝试一下，但是给我一个错误的语法

declare @EmpId int
SELECT  dbo.Employee.Id as @EmpId FROM  dbo.Employee

3 个解决方案

#1

select @EmpID = ID from dbo.Employee

Or

或

set @EmpID =(select id from dbo.Employee)

设置@EmpID =(从dbo.Employee中选择id)

Note that the select query might return more than one value or rows. so you can write a select query that must return one row.

注意，select查询可能返回多个值或行。因此，您可以编写一个select查询，该查询必须返回一行。

If you would like to add more columns to one variable(MS SQL), there is an option to use table defined variable

如果您想向一个变量(MS SQL)添加更多的列，那么可以选择使用表定义的变量。

DECLARE @sampleTable TABLE(column1 type1)
INSERT INTO @sampleTable
SELECT columnsNumberEqualInsampleTable FROM .. WHERE ..

As table type variable do not exist in Oracle and others, you would have to define it:

由于Oracle等中不存在表类型变量，所以需要对其进行定义:

DECLARE TYPE type_name IS TABLE OF (column_type | variable%TYPE | table.column%TYPE [NOT NULL] INDEX BY BINARY INTEGER;

-- Then to declare a TABLE variable of this type: variable_name type_name;

——然后声明此类型的表变量:variable_name type_name;

-- Assigning values to a TABLE variable: variable_name(n).field_name := 'some text';

——为一个表变量赋值:variable_name(n)。field_name:= '文本';

-- Where 'n' is the index value

——其中'n'是索引值

#2

Assuming such a query would return a single row, you could use either

假设这样的查询将返回一行，您可以使用其中任何一个

select @EmpId = Id from dbo.Employee

Or

或

set @EmpId = (select Id from dbo.Employee)

#3

This is how to assign a value to a variable:

这是如何给变量赋值:

SELECT @EmpID = Id
  FROM dbo.Employee

However, the above query is returning more than one value. You'll need to add a WHERE clause in order to return a single Id value.

但是，上面的查询返回的值不止一个。为了返回单个Id值，需要添加WHERE子句。