SQL Server declare variable

Question

I use

Insert into table 
    select (col_name) 
    from another

For example :

insert into table1(salary) 
    select salary 
    from table2

If (variable=700)
Begin
Insert .......
End

Can I declare a variable and let the value of it equals the salary

While select row number 1 , the value is the salary in row 1

while selecting row number 2 , the value of the variable is the salary in row number 2

And so on?

Edit your question and provide sample data and desired results. It is unclear what you are trying to accomplish. — Gordon Linoff
– Gordon Linoff, Commented Dec 27, 2015 at 21:55

Alexei - check Codidact · Accepted Answer · 2015-12-28 07:09:19Z

3

From the code provided, @variable is a scalar, but SELECT salary from table2 will return a result set with one column, so direct assignment is not possible or it will fetch only the last (thanks Ken for pointing out) value from your SELECT.

If I understand correctly what you are trying to do, I think you want something like:

INSERT INTO ...
SELECT 
   (CASE WHEN salary = 700 THEN ...
     WHEN salary = ... THEN ...)
     ELSE ... END)

Or maybe it can be done even better, if you provide us the whole thing you are trying to do.

edited Dec 28, 2015 at 7:09

answered Dec 27, 2015 at 21:49

Alexei - check Codidact

23.2k22 gold badges159 silver badges179 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ken Clement Over a year ago

You are absolutely correct in your answer above though there is a minor error in your parenthetical statement in the first sentence. A direct assignment to a scalar in a multi row result set will assign to the scalar the LAST, not the first, value. The following quick-and-dirty query verifies this declare @object_id int; select @object_id = object_id from sys.objects order by object_id asc; select @object_id. This query will return the LAST object_id not the first one.

Collectives™ on Stack Overflow

SQL Server declare variable

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related