• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 7124
  • Last Modified:

SQL Loop Through Comma Separated Variable?

I need to Update a table having dynamically created column names. I have the names in a comma-delimited @Columns variable, filled by a cursor during column creation. I don't know MS SQL can use a 'For' or 'Foreach' against a comma-delimited variable string.  I have some code below, it's missing the column-name-extraction syntax... Help

Note: columns names are 4-digit part numbers
Note: update appends a plus(+) to the column data which shows a price increase

Declare @ID = 1201
Declare @Columns      varchar(max) = '1495, 1579, 4578, 9147, 17493, 6482'
Declare @Column            varchar(30)
Declare @i      int = 1
Declare @Sql varchar(max)

While (@i < @Count)            
   Begin                  

       @Column = ??????

        Set @Sql  = 'Update a Set a.' + @Column + ' = ' + @Column + '' +''
                             + ' From #PartNumbers a
                            where ID = ' + CAST(@ID AS VARCHAR) ;
      Exec(@Sql);
   End                  
Set @i = @i + 1
End
0
WorknHardr
Asked:
WorknHardr
  • 3
  • 3
  • 2
2 Solutions
 
Surendra NathCommented:
Hi

Inorder to accomplish this task, you first need to create a split function the code is pasted here for your convinience, if you want to know further about how this function works you can visit my blog here http://everysolution.wordpress.com/2011/07/28/udf-to-split-a-delimited-string-and-return-it-as-a-table/
 
Split Function

CREATE FUNCTION dbo.Split
(
 @RowData nvarchar(2000),
 @SplitOn nvarchar(5)
)
RETURNS @RtnValue table
(
 Id int identity(1,1),
 Data nvarchar(100)
)
AS
BEGIN
 Declare @Cnt int
 Set @Cnt = 1
 DECLARE @index INT
 SET @index = Charindex(@SplitOn,@RowData)
 While (@index>0)
 Begin
 Insert Into @RtnValue (data)
  Select
 Data = ltrim(rtrim(Substring(@RowData,1,@index-1)))
 
 Set @RowData = Substring(@RowData,@index+1,len(@RowData))
 Set @Cnt = @Cnt + 1
 SET @index = Charindex(@SplitOn,@RowData)
 End
 
 Insert Into @RtnValue (data)
 Select Data = ltrim(rtrim(@RowData))
 
 Return
END

Open in new window



Re-writing your code

Declare @ID = 1201
Declare @Columns      varchar(max) = '1495, 1579, 4578, 9147, 17493, 6482'
Declare @Column            varchar(30)
Declare @i      int = 1
Declare @Sql varchar(max)
Declare @Data VARCHAR(10)

DECLARE @t TABLE
(
  Data VARCHAR(10)
)

INSERT INTO @T
SELECT DATA FROM dbo.Split(@Columns,',')

DECLARE T CURSOR FOR
SELECT Data From @t 

FETCH T INTO @Data

WHILE @@FETCH_STATUS = 0
BEGIN

Set @Sql  = 'Update a Set a.[' + @Data + '] = [' + @Data + ']' + '' +'' 
                    + ' From #PartNumbers a 
                  where ID = ' + CAST(@ID AS VARCHAR) ;
Exec(@Sql);
FETCH T INTO @Data
End                  

CLOSE T
DEALLOCATE T

Open in new window

0
 
Scott PletcherSenior DBACommented:
Declare @ID int
Declare @Columns varchar(max)
Declare @Column varchar(30)
Declare @Column_End int
Declare @Sql varchar(max)

SET @ID = 1201
SET @Columns = '1495, 1579, 4578, 9147, 17493, 6482'

SET @Sql = 'UPDATE a
SET'

WHILE LEN(@Columns) > 0
BEGIN
    SET @Column_End = CHARINDEX(',', @Columns)
    IF @Column_End = 0
        SET @Column_End = LEN(@Columns) + 1
    SET @Column = LTRIM(RTRIM(LEFT(@Columns, @Column_End - 1)))
    SET @Sql = @Sql + ' a.[' + @Column + '] = [' + @Column + '] + ''+'','
    SET @Columns = SUBSTRING(@Columns, @Column_End + 1, 2000000000)
END --WHILE

SET @Sql = LEFT(@Sql, LEN(@Sql) - 1) + '
FROM dbo.tablename a
WHERE a.ID = ' + CAST(@ID AS varchar(10))

PRINT @Sql
--EXEC(@Sql)
0
 
WorknHardrAuthor Commented:
>>ScottPletcher

Q. Could your code be used a Function?
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
Scott PletcherSenior DBACommented:
Not really.  It's custom code to match your stated requirements.   A function can't update a permanent table anyway.

I can give you code for a very high-performance splitter function that is an inline-table-valued function.  That's extremely good for splitting columns values from a table, where the function might have to be called thousands or millions of times.
0
 
WorknHardrAuthor Commented:
I'm updating a #Temp table and I like to use reusable Functions when ever possible.
0
 
Scott PletcherSenior DBACommented:
My earlier statement wasn't broad enough -- a SQL Server function can't update any table.

Reusing code for a temp table will be tricky even in a proc, but it could be done.
0
 
Surendra NathCommented:
@worknHardr -- did you consider using my solution as it seems it has a function that you might need
0
 
WorknHardrAuthor Commented:
Both code examples are great, thx
0

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

  • 3
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now