I’m working on a stored procedure in SQL Server 2008 and I’m trying to find a way to store query results directly into a variable. Here’s what I want to do:
DECLARE @CustomerInfo TABLE
(
Id uniqueidentifier,
Name nvarchar(50),
Email nvarchar(100)
);
SELECT
Id,
Name,
Email
INTO
@CustomerInfo
FROM
Users
WHERE
Id = @UserId
But this doesn’t work. SQL Server doesn’t like it. I know I can use separate variables for each column, but that feels clunky. Is there a better way to grab multiple columns from a single row and store them for later use in the procedure? I want to avoid running multiple queries on the same table if possible. Any ideas?
While SQL Server doesn’t allow direct insertion of query results into a table variable as you’ve shown, there’s a workaround using INSERT INTO. Here’s how you can achieve what you’re looking for:
DECLARE @CustomerInfo TABLE
(
Id uniqueidentifier,
Name nvarchar(50),
Email nvarchar(100)
);
INSERT INTO @CustomerInfo (Id, Name, Email)
SELECT Id, Name, Email
FROM Users
WHERE Id = @UserId;
This method populates your table variable with the query results. You can then use @CustomerInfo throughout your stored procedure as needed. It’s efficient and avoids multiple queries on the Users table. Remember, table variables are best for small result sets. For larger datasets, consider using a temporary table instead.
hey mate, try using a cursor: declare vars, open a cursor on your query, fetch results, then close it. not as effiecient but works fine. hope it helps!