SQL Server: Selecting Multiple Columns for Multiple Variables in a Stored Procedure

Question

In MySQL, the following works:

DECLARE myInt INT(20); DECLARE myDate DATETIME; SELECT someInt, someDate INTO myInt, myDate FROM someTable WHERE myName = someName 

Is it possible to do something similar in SQL Server?

SQL Server complaints about "Invalid syntax" in the INTO line that uses local @variables.

Answer 1

I don’t know if I understand your problem. If you want to set several variables at once:

 DECLARE @myInt INT; DECLARE @myDate DATETIME; SELECT @myInt = someInt, @myDate = someDate FROM someTable WHERE myName = someName 

Also note that if a select selects many rows, the variables will contain the last row values. In fact, the variables will be set for each row selected.

Also note that in SQL Server you do not have a parameter for an Int declaration. Perhaps you want to use Decimal (20) .

Answer 2

Yes, do it as shown below. Also, the @ prefix to declare the parameter.

 DECLARE @myInt INT; DECLARE @myDate DATETIME; SELECT @myInt = someInt, @myDate = someDate FROM someTable WHERE myName = someName