SQL Concat Variable Cast at Runtime Returns NULL?

I'm trying to build a string of converted decimals into variable @Prices and keep getting null returns when I call this code in a separate query window:

   Declare @Prices      varchar(max) = NULL
   exec sp_MyTableTest @Prices OUTPUT
   select @Prices

   ---- Return ------
    NULL

ALTER PROCEDURE [dbo].[SP_MyTableTest]
(
	@Prices	 varchar(max) OUT
)

AS

BEGIN

Declare @Price		varchar(10)
Set @Prices = ''

Set @MyCursor = Cursor For Select Code, Name, Cast(Price as varchar(10) ) 
                                                  from tbl_Products 
        Open @MyCursor

	Fetch Next From @MyCursor
        Into @Price	

	While @@FETCH_STATUS = 0

		Begin
 
                        Set @Prices = @Prices + '''' + @Price + ''',' // Always returns Null

                        Set @Prices = ''''+ @Price + ''',' //WORKS!

                	Fetch Next From @MyCursor
                        Into @Name	
                End	

        Close @MyCursor
        Deallocate @MyCursor

Open in new window

WorknHardrAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
this should work better:
ALTER PROCEDURE [dbo].[SP_MyTableTest]
(
	@Prices	 varchar(max) OUT
)

AS

BEGIN

Declare @Price		varchar(10)
Set @Prices = ''

Set @MyCursor = Cursor For 
Select Cast(Price as varchar(10) ) as price 
 from tbl_Products 
  where price is not null

        Open @MyCursor

	Fetch Next From @MyCursor
        Into @Price	

	While @@FETCH_STATUS = 0

		Begin
 
                        Set @Prices = isnull(@Prices,'') + '"' + @Price + '",'

                	Fetch Next From @MyCursor
                        Into @price	
                End	

        Close @MyCursor
        Deallocate @MyCursor
                                  

Open in new window

0
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
you could shorten the code also, and avoid the cursor altogether:
ALTER PROCEDURE [dbo].[SP_MyTableTest]
(
	@Prices	 varchar(max) OUT
)

AS

BEGIN

Select @Prices = isnull(@prices, '') + '"' + Cast(Price as varchar(10) ) + '",'  
 from tbl_Products 
  where price is not null

END

Open in new window

0
 
WorknHardrAuthor Commented:
Excellent`a... thx
0
All Courses

From novice to tech pro — start learning today.