What can I use instead of #Temp table in sql function

Viewed 43363

Here is my sql query.

CREATE FUNCTION UF_GetOrderProducts
(
    @OrderId int
)
RETURNS VARCHAR(500)
AS
BEGIN
    SELECT  Identity(int,1,1) ID, ProductId INTO #Temp  FROM OrderProduct WHERE OrderId = @OrderId

Declare @Id int,
        @Count int,
        @LoopCount int,
        @ProductList VARCHAR(500),
        @ProductListTemp VARCHAR(500)

SET @Count = (Select Count(*) From #Temp)

SET @LoopCount = 1
 SET @ProductList = ''
WHILE @LoopCount <= @Count
BEGIN



     SET @ProductListTemp =( SELECT Name FROM Product WHERE ProductId =(Select ProductId from #Temp Where ID = @LoopCount))
       SET @ProductList +=@ProductListTemp + '<br/>'
       Set @LoopCount=@LoopCount + 1         


END
DROP TABLE #Temp

RETURN @ProductList

END
GO

I have to loop in #Temp Table. Do you have any other suggestions?

1 Answers
Related