Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL Concat Variable Cast at Runtime Returns NULL?

Posted on 2014-01-23
3
Medium Priority
?
326 Views
Last Modified: 2014-01-23
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

0
Comment
Question by:WorknHardr
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 1600 total points
ID: 39804764
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
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 1600 total points
ID: 39804769
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
 

Author Closing Comment

by:WorknHardr
ID: 39804843
Excellent`a... thx
0

Featured Post

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

715 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question