SQL Server : selecting multiple columns into multiple variables in a stored procedure

Question

The following works in MySQL:

DECLARE myInt INT(20);
DECLARE myDate DATETIME;

SELECT someInt, someDate
INTO myInt, myDate
FROM someTable
WHERE myName = someName

Is it possible to do something like that in SQL Server?

SQL Server complaints about "Incorrect syntax" in the INTO line f that is attempted local @variables.

Answer 1

Not sure if I understand your issue. If you want to set multiple variables at once:

DECLARE @myInt INT;
DECLARE @myDate DATETIME;

SELECT @myInt = someInt, @myDate = someDate
FROM someTable
WHERE myName = someName

Also note if the select fetches many rows the variables will hold the last rows values. In fact the variables will be set for each row fetched.

Also note in SQL Server you don't have a parameter to the Int declaration. Maybe you want to use Decimal(20).

Answer 2

Yes, do it like below. Also, prefix @ sign for declaring parameter.

DECLARE @myInt INT;
DECLARE @myDate DATETIME;

SELECT @myInt = someInt, @myDate = someDate
FROM someTable
WHERE myName = someName