0

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

stoner
  • 417
  • 2
  • 12
  • 22
  • Storing delimited data in a database is generally a bad idea. It means you have to split that back into usable values. You can use FOR XML to generate your delimited list but you will soon be back asking how to split the list. There are better options like table valued parameters or a number of other options instead delimited lists. – Sean Lange Feb 08 '16 at 18:55
  • Possible duplicate of [Concatenate many rows into a single text string?](http://stackoverflow.com/questions/194852/concatenate-many-rows-into-a-single-text-string) – AHiggins Feb 08 '16 at 18:57
  • Is there a way to Convert SQL Server result set(many row) into string – stoner Feb 08 '16 at 18:59
  • @AHiggins i look the link it's interested – stoner Feb 08 '16 at 19:00

1 Answers1

1

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
Siyual
  • 16,415
  • 8
  • 44
  • 58
  • Don't worry by comma , i have another function that split this VARCHAR based on the comma.i'll test that – stoner Feb 08 '16 at 19:04