How do I store the select column in a variable?
I tried this, but it throws me an error "Incorrect syntax":
declare @EmpId int
SELECT dbo.Employee.Id as @EmpId FROM dbo.Employee
This question is related to
sql-server
sql-server-2008
select @EmpID = ID from dbo.Employee
Or
set @EmpID =(select id from dbo.Employee)
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.
If you would like to add more columns to one variable(MS SQL), there is an option to use table defined variable
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:
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;
-- Assigning values to a TABLE variable: variable_name(n).field_name := 'some text';
-- Where 'n' is the index value
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.
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)
Source: Stackoverflow.com