CODEWITHSUNDEEP
sql-servert-sqlstored-procedures
feetwet
feetwet

Reputation: 3446

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

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.

Upvotes: 8

Views: 9375

Answers (2)

jean
jean

Reputation: 4350

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).

Upvotes: 11

Rahul
Rahul

Reputation: 77846

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

Upvotes: 1

Related Questions