Only one expression can be specified in the select list when the subquery is not introduced with EXISTS. - DECLARE to use SPLIT_ROW

Question

I have that query:

DECLARE @test AS varchar = 
(select * from users where Usr_ID in 
(select Doc_Shortstringcolumn1 from Documents where Doc_ID = 11931))

And I've got an error "Only one expression can be specified in the select list when the subquery is not introduced with EXISTS." The result of select statement are three numbers, which i need to split into three rows.

`@test` is a *scalar* variable. It can contain *one* value. You're retrieving multiple columns (and potentially multiple rows) with your `select *`. — Damien_The_Unbeliever, Sep 11 '18 at 08:48
instead of `(select *...` in the subquery specify a single column. you can't assign multiple columns to `@test`. It should be something like `(select top 1 UserId from Users....` to ensure a single value is set. — Tanner, Sep 11 '18 at 08:48
Possible duplicate of [Only one expression can be specified in the select list when the subquery is not introduced with EXISTS](https://stackoverflow.com/questions/1904314/only-one-expression-can-be-specified-in-the-select-list-when-the-subquery-is-not) — Tanner, Sep 11 '18 at 08:53
**Always** specify the length of a (n)varchar. Try `DECLARE @test VARCHAR = 'blabla' SELECT @test`. — HoneyBadger, Sep 11 '18 at 09:07

score 0 · Accepted Answer · answered Sep 11 '18 at 08:49

0

 (select * from users where Usr_ID in 
   (select Doc_Shortstringcolumn1 from Documents where Doc_ID = 11931))

you query returns multiple rows as a result variable can not contain multiple rows value.

if your query just return one value then it will return correct result

but if you change your query like below then it will works

DECLARE @test AS varchar = 
    (select top 1 Doc_Shortstringcolumn1 from users where Usr_ID in 
    (select Doc_Shortstringcolumn1 from Documents where Doc_ID = 11931)
     )

answered Sep 11 '18 at 08:49

Zaynul Abadin Tuhin

31,407
5
33
63

Thanks, this works, but i've got new error: DECLARE @test AS nvarchar = (select top 1 Usr_ID from users where Usr_ID in (select Doc_Shortstringcolumn1 from Documents where Doc_ID = 11931) ) and the error is "Conversion failed when converting the varchar value '49,1,8' to data type int." – bambosz Sep 11 '18 at 09:03
@bambosz if it helps then accept answer and your date is string like "49,1,8" then it is normal to fail conversion ,you have to split that string as a integer then conversion need, use split function and split your varchar to int the do type cast – Zaynul Abadin Tuhin Sep 11 '18 at 09:06

score 0 · Answer 2 · answered Sep 11 '18 at 09:00

Looking at your Query, It seems that you are trying to store a table into a variable which is of Varchar data type which you cannot do in SQL Server.

There are 2 possible solutions

1.

You May select only the Required filed instead of the * ad in that case if there are more than one row is returned, then only the first one will be stored in the variable and all other values will be ignored.

If this is ok with you then you may go ahead with this approach

DECLARE @test AS varchar = 

select 
    @test = YourColumnName 
    from users where Usr_ID in 
    (
        select Doc_Shortstringcolumn1 from Documents where Doc_ID = 11931
    )

2

The Second approach is to use a table variable or a Temporary table to store the values so that you can store all the values and retrieve the same when needed.

Using Temp Table

select 
    *
    into #temp
    from users where Usr_ID in 
    (
        select Doc_Shortstringcolumn1 from Documents where Doc_ID = 11931
    )

using Table Variable

DECLARE @test AS TABLE
(
    Column1 VARCHAR(50),
    Column2 VARCHAR(50),
    Column3 VARCHAR(50)
) 

INSERT INTO @Test
(
    Column1,
    Column2,
    Column3
)
select 
    YourColumnName1,
    YourColumnName2,
    YourColumnName3
    from users where Usr_ID in 
    (
        select Doc_Shortstringcolumn1 from Documents where Doc_ID = 11931
    )

Only one expression can be specified in the select list when the subquery is not introduced with EXISTS. - DECLARE to use SPLIT_ROW

2 Answers2