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

x
?
Solved

Cannot concatenate a string in t-SQL

Posted on 2004-09-21
6
Medium Priority
?
2,698 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
[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
6 Comments
 
LVL 17

Accepted Solution

by:
BillAn1 earned 260 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 240 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
10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

 
LVL 17

Expert Comment

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

Expert Comment

by:Eugene Z
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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

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.
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 set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

722 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