CODEWITHSUNDEEP
sql-server
CSKR
CSKR

Reputation: 33

Resolve query containing multi columns and subquery return more than 1 value

I have a very simple table with value:

CREATE TABLE multiplication(
        a SMALLINT, b SMALLINT);
INSERT INTO multiplication(a, b)
VALUES (1, 2), (2, 4), (3, 3)

When I'm trying to print Result by

DECLARE @Result INT;
SELECT @Result = (SELECT a, b, a * b AS c FROM multiplication)
PRINT @Result

I got the error

Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

I know I get this error because I'm trying to assign two columns to one variable. Then I'm remove other columns and get the error

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

I also try with select top but no luck. So how can get result where query containing multi columns and subquery return more than 1 value?

Upvotes: 0

Views: 39

Answers (1)

pwilcox
pwilcox

Reputation: 5763

Are you just trying to see your results? Then just select the values without putting it into a variable:

SELECT a, b, a * b AS c FROM multiplication

If you really want it into a variable, then Sql Server has special variable types called table variables. Declare one like this:

declare @Results table (
    a smallint,
    b smallint,
    c smallint
);

Then insert into it like you would any other type of table:

insert @Results
SELECT a, b, a * b AS c FROM multiplication

Upvotes: 2

Related Questions