Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Cannot concatenate a string in t-SQL

Posted on 2004-09-21
6
Medium Priority
?
2,701 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 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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how the fundamental information of how to create a table.

926 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