Home Ask Login Register

Developers Planet

Your answer is one click away!

stoner February 2016

Set more than 1 value to variable from Select statement

I'm trying to set varchar variable like this :

set     @var = (SELECT name from Tmptable AS name )

I want this variable store value like this : 'name1,name2,name3,...' This returns an error like the one in the title: Subquery returned more than 1 value.

I use this variable in paramter of on function

Select *
Into Tmp2
FROM [dbo].[MyFunction](@var, DEFAULT)  AS PARAM

I know we can't put more than value in a variable.I need that Variable represent More Than One Value by split value to ","

Any regard in this regards


Siyual February 2016

You can do this using STUFF and FOR XML:

Set     @var = 
    SELECT  Distinct Stuff((Select ',' + Name From Tmptable For Xml Path ('')), 1, 1, '') 
    From    Tmptable

But, ideally, you shouldn't be storing comma separated values in a VARCHAR. Using a table-variable would likely work better:

Declare @Var Table
    Name    NVarchar (40)

Insert  @Var (Name)
Select  Name
From    TmpTable

Post Status

Asked in February 2016
Viewed 2,053 times
Voted 7
Answered 1 times


Leave an answer

Quote of the day: live life