Help! I am trying to concantenate a single return line from table values in SQL Server 2005. I can get close, but not get any version of my code to work. I had a stored procedure working, but client needed ability to make multiple selections. So I am trying to rework the code into a select parameter.
This is current version:
Declare @t Table
(YouthID int,
CurMeds nvarchar(150))
Declare @CurMed varchar(100)
Declare @ArrayList varchar(200)
Set @ArrayList = ' '
Insert into t (YouthID,CurMeds)
Select YouthID, MedDosage + ' ' + MedDescription As CurMeds
From TblMedicatiions
Where (YouthID in(@YouthID))
if (@@ROWCOUNT = 0)
begin
insert into @t (YouthID, CurMeds)
values (@YouthID, 'Currently w/o Medications')
end
Declare curMed CURSOR FAST_FORWARD, READ_ONLY FOR
Select curMeds from @t Where YouthID = @YouthID
OPEN curMed
FETCH NEXT FROM curMed INTO @CurMed
-- start of cursor
WHILE @@FETCH_STATUS = 0
BEGIN
Select @ArrayList = @ArrayList + CAST(@CurMed AS varchar(50))+', '
FETCH NEXT FROM curMed INTO @CurMed
END
SET @ArrayList = SUBSTRING(@ArrayList,1,DATALENGTH(@ArrayList)-2)
CLOSE curMed
DEALLOCATE curMed
I receive a SQL Server Error: 102 near ','. Tried removing the +', ', but still received the same. Error.
Thanks in advance.
Terry
Trying a concantenate a set of results from a query in SQL Server with multivalue parameter
jojolimited
Found first error was a misplaced comma, put code into a stored procedure and came up with a better error message and fixed the issue.
I now have an error with incorrect input parameters to the table only if I send multivalued parameters through. Error is more insert parameters than fields. So I still could use help.
Thanks!
Terry
Chirag Patel
KimI
hi terry,
i hope the use of ',' is only to delimited the various aray elements,
i have only seen ur query, i would suggest u to use the delimiter ',' as follows
Select @ArrayList = @ArrayList + ','+ CAST(@CurMed AS varchar(50))
but then when u are entering the First element then ur array would be
having a ',' before the first array element, u can use a case statement to check that the first element if it a ',' should be replaced by a '' .
i hope i answered ur question.
iam sorry for missing the point,
regards
www.snktheone.com
Chris Webb
I tried taking the ', " off the end and still got the error. So I have to believe this is something else, but will try the suggestion and post the result.
Thanks!
Terry
CJ Butcher
skinnyl
I am just trying to get the code to run at the current time. I keep getting the errors I had listed. The length of the variable can be adjusted at a later date. I would probably use some RTRIM first.
Thanks!
Terry