How do I loop a #Temp Table and String values together in a variable?

[Brian Connelly]

I am writing a stored procedure and have a temp table that is dynmaic in size when it executes. I want to loop through each row in the temp and take a value from a column and then append that value in a varaible that would hold each value from all the rows. I am having trouble doing this. I can see the values, however, I get null for my return. Here is my code and Thanks in advance:

CREATE TABLE #TEMP
(
ID INT Identity,
ShortDescription INT,
)
INSERT INTO #TEMP 
EXEC spRPProjectTemp 99
DECLARE @var As VarChar(MAX) 
DECLARE @Counter As INT
SET @Counter = 1
DECLARE @Index AS INT
SET @Index = (SELECT MAX(ID) FROM #TEMP)
DECLARE @varContainer AS Varchar(30)
 
WHILE @Counter < @Index + 1
BEGIN
      SET   @varContainer = (SELECT ShortDescription FROM #TEMP WHERE ID = @Counter)
IF @var = ''
BEGIN
      SET @var = @varContainer
SET @Counter = @Counter + 1
END
ELSE
BEGIN
      SET @var = @var+', '+@varContainer
      SET @Counter = @Counter + 1
END
END
 
SELECT @var AS 'String'
SELECT * FROM #TEMP 

[Rabbit]

You should get an error. I don't know why you're not. The reason is that ShortDescription is a integer and you have not cast it to varchar for your string. That is the underlying problem.

However,in SQL, you should avoid loops whenever possible. And if you have to loop on a recordset, use a cursor.

In this situation, if you are on SQL 2005 and above, you can use the XML capabilities to concatenate your values without having to use a loop of any kind.

SELECT ', ' + CAST(ShortDescription AS VARCHAR(30)
FROM #temp
FOR XML PATH('')

You can get rid of the first comma and space with the STUFF function if you wish.

[ck9663]

Here are some ideas on how to concatenate rows into a delimited string.

Happy Coding!!!


~~ CK

[Brian Connelly]

Thank you for the help.