Reputation: 407
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
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