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 :

DECLARE @var NVARCHAR(40)
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

Answers


Siyual February 2016

You can do this using STUFF and FOR XML:

DECLARE @var NVARCHAR(40)
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

Search




Leave an answer


Quote of the day: live life