Solved

Cannot concatenate a string in t-SQL

Posted on 2004-09-21
6
2,692 Views
Last Modified: 2012-05-05
I'm passing a comma separated string to my stored procedure, looping through it and trying to concatenate a string (sql). The loop is working as i can print each individual item as i go through it - but the variable that i am concatenating prints as blank after the loop - what is going wrong? here is the test code:

CREATE procedure wsp_search
@table varchar(32),
@list varchar(128),
@allwords int

AS

declare @sql varchar(4000),@term varchar(10), @pos int

begin
      set nocount on

      set @list = ltrim(rtrim(@list))+ ','
      set @pos = charindex(',', @list, 1)

      if replace(@list, ',', '') <> ''
      begin
            while @pos > 0
                  begin
                        set @term = ltrim(rtrim(left(@list, @pos - 1)))
                        print len(@term)
                        if len(@term)>0
                              begin
                                    --construct sql here
                                    select @sql = @sql  + '|||' + @term
                                    --select @sql=@term *this is ok but above is not!!!*
                                    --print @sql
                              end
                        set @list = right(@list, len(@list) - @pos)
                        set @pos = charindex(',', @list, 1)
      
                  end
      end
      print @sql
      --exec(@sql)      
end

GO
0
Comment
Question by:lildrc
6 Comments
 
LVL 17

Accepted Solution

by:
BillAn1 earned 65 total points
ID: 12114276
you have "select @sql = @sql  + '|||' + @term"
but you never initialise @sql. It's initial value will be NULL, and concatinataing NULL with something else results in NULL. You just need to add an initial line of code to set @sql = '' before you go into the loop.

CREATE procedure wsp_search
@table varchar(32),
@list varchar(128),
@allwords int

AS

declare @sql varchar(4000),@term varchar(10), @pos int

begin
     set nocount on

     set @sql = ''

     set @list = ltrim(rtrim(@list))+ ','
     set @pos = charindex(',', @list, 1)

     if replace(@list, ',', '') <> ''
     begin
          while @pos > 0
               begin
                    set @term = ltrim(rtrim(left(@list, @pos - 1)))
                    print len(@term)
                    if len(@term)>0
                         begin
                              --construct sql here
                              select @sql = @sql  + '|||' + @term
                              --select @sql=@term *this is ok but above is not!!!*
                              --print @sql
                         end
                    set @list = right(@list, len(@list) - @pos)
                    set @pos = charindex(',', @list, 1)
     
               end
     end
     print @sql
     --exec(@sql)    
end
0
 
LVL 15

Assisted Solution

by:jdlambert1
jdlambert1 earned 60 total points
ID: 12114279
You're not initializing @sql. Try this:

declare @sql varchar(4000),@term varchar(10), @pos int
set @sql = ''
0
 
LVL 15

Expert Comment

by:jdlambert1
ID: 12114291
You're still fast on the draw, Bill!
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 17

Expert Comment

by:BillAn1
ID: 12114542
dead heat, I think!!
0
 
LVL 42

Expert Comment

by:EugeneZ
ID: 12114580
I got your goal- why do not use REPLACE function:

select '|||'+ replace ('1,2,3,4,5,6,7',',','|||')
--result |||1|||2|||3|||4|||5|||6|||7
0
 

Author Comment

by:lildrc
ID: 12115707
looks good guys - that was puzzling me for ages! - i'll try it out at work tomorrow and issue the points.
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Viewers will learn how the fundamental information of how to create a table.
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…

786 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