CODEWITHSUNDEEP
sqlsql-servert-sqlsubquery
stoner
stoner

Reputation: 407

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

Upvotes: 0

Views: 2908

Answers (1)

Siyual
Siyual

Reputation: 16917

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

Upvotes: 1

Related Questions