?
Solved

SQL 2008 Concat Variable Using Cursor Loop Error?

Posted on 2014-01-22
6
Medium Priority
?
575 Views
Last Modified: 2014-01-23
I'm trying to build a string like so using the Cursor below: [Jim], [Bob], [Tom]

I receive the following error using Concat and SQL 2008:

   'Concat' is not a recognized built-in function name.

I'm calling the SP like so in a separate query window:
   Declare @Columns      varchar(255)
   Exec sp_MyTableTest 1469, @Columns OUTPUT
   Select @columns

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

ALTER PROCEDURE [dbo].[SP_MyTableTest]
(
	@Agency int,
	@Columns	varchar(255) OUT
)

AS

BEGIN

Declare @Name		varchar(30)

Set @MyCursor = Cursor For Select Code, Name, Price from tbl_Products 
        Open @MyCursor

	Fetch Next From @MyCursor
        Into @Name	

	While @@FETCH_STATUS = 0

		Begin
                         Set @Columns = Concat( ',[' + @Name + ']' ) // Error
 
                         Set @Columns +=  ',[' + @Name + ']'  // Always returns Null

                	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
  • 4
  • 2
6 Comments
 
LVL 35

Expert Comment

by:Robert Schutt
ID: 39801995
It seems to me that you only have to initialize @Columns to an empty string first as NULL + string = NULL.
0
 
LVL 35

Expert Comment

by:Robert Schutt
ID: 39801997
PS: I'm getting an error on the cursor when I test your code but I ignored that as I have a feeling this is not the exact code you're using (Name <=> Column).
0
 
LVL 35

Accepted Solution

by:
Robert Schutt earned 1600 total points
ID: 39802000
PS2: I didn't test CLR version, only
Set @Columns = @Columns + ',[' + @Name + ']'

Open in new window

0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

Author Comment

by:WorknHardr
ID: 39803577
yea, always null:

   Set @Columns = @Columns + ',[' + @Name + ']'
0
 
LVL 35

Assisted Solution

by:Robert Schutt
Robert Schutt earned 1600 total points
ID: 39803589
Ok, so have you added an init?

just add a line below "declare @Name" for example:
Set @Columns = ''

Open in new window

0
 

Author Closing Comment

by:WorknHardr
ID: 39803811
That worked! thx

Declare @Name      varchar(30)
Set @Columns = ''

Set @Columns = @Columns + ',[' + @Name + ']'
0

Featured Post

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

765 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