How to loop in SQL from list

I need to know how to loop through a list in sql that I would provide.  In short I have an array like this:

'data1,data2,data3'

I need to create a loop for this in sql that I can run a update statement with for example:

BEGIN
update table set datafield =
FETCH next index value
END

I don't have that much experience with sql scripting and have found no solid examples on internet so far.

Any help here , greatly appreciated.

Thanks.

Brad
harumbAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

BinuthCommented:
" loop through a list in sql "
could you explain more ?
0
harumbAuthor Commented:
Well I program in perl and I have a list of values that I have to update a table with like this:

my @array = ("code1,code2,code3);

What I want to do is create a script dynamically that I will pass to my sql server so that it will loop through my list and update the table.  Right now I do an update for each code from my program and it is time consuming for the process.  Below is an example of what I am doing in my perl program;

for my $index (@array) { ## index is the place holder for the array element
   my $sql = "update table set code = $index where something = something";
  $DBH->Sql($sql);
}

I need to do this but on the Sql server side.

Does this make sense?

thanks.
0
BinuthCommented:
first you create the function(see attached) called 'fnSplitterforString' in sql server and then use the following stored procedure



CREATE PROCEDURE UpdateList(@Values VARCHAR(8000))
AS
BEGIN
      
      SET NOCOUNT ON;

      select rownum,id
      INTO #Tmp
      from fnSplitterforString(@Values,',')

      declare @iRowIndex as int
      set @iRowIndex = 1

      declare @iMaxIndex as int
      select @iMaxIndex=max(rownum) from #Tmp

      while @iRowIndex <= @iMaxIndex
      begin

            update tableName
            set code = (select id from #Tmp where rownum = @iRowIndex)
            Where <Your contitions>

            set @iRowIndex = @iRowIndex + 1
      end

END
GO
CREATE FUNCTION [dbo].[fnSplitterforString] 
(
	 @IDs Varchar(8000) 
	,@chrDelimiter CHAR(1)
)  
RETURNS @Tbl_IDs Table  (RowNum INT,ID VARCHAR(1000))  AS  
BEGIN 
	 
	 SET @IDs =  @IDs + @chrDelimiter
	 
	 DECLARE @Pos1 INT
	 DECLARE @pos2 INT
	 DECLARE @RowNum INT
	 
	 
	 SET @Pos1=1
	 SET @Pos2=1
	 SET @RowNum = 1	
	 WHILE @Pos1<Len(@IDs)
	 BEGIN
	
		  SET @Pos1 = CharIndex(@chrDelimiter,@IDs,@Pos1)
		  INSERT @Tbl_IDs SELECT  @RowNum,Cast(Substring(@IDs,@Pos2,@Pos1-@Pos2) AS VARCHAR(1000))
		  SET @Pos2=@Pos1+1
		  SET @Pos1 = @Pos1+1
		  SET @RowNum = @RowNum + 1	
	 END 
	 RETURN
End

Open in new window

0
C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

harumbAuthor Commented:
Wow, that is more complicated than I was hoping for.  I found this snippet of code:

declare @code_id varchar(20), 'TONSIL,RT' UCC'
declare my_cursor cursor @code_id
open my_cursor
fetch next from my_cursor into @code_id
WHILE @@FETCH_STATUS = 0
BEGIN
      print 'code_id'
    FETCH NEXT FROM my_cursor
    INTO @code_id
END

CLOSE my_cursor
DEALLOCATE my_cursor

What I need to do is fix this so "code_id" inside the loop holds the next array value I was trying to declare  above.  I keep getting an error though, does this type of code look familiar can you show me what I am doing wrong.  Also in the code you sent me, where do I define the array of values like ("code1, code2, code3)..

thanks.

brad
0
harumbAuthor Commented:
I also found this snippet of code:

declare @i int
while (@i<10)
begin
insert into dbname.dbo.table_name values @i
@i=@i+1
end


What I need is to replace @i with a list like (code1,code2,code3) and then a way to incorporate that into the loop.

thanks.

0
BinuthCommented:
i am assuming that you are passing comma separated values from your perl application to SQL,and there is array like objects in SQL server(use #tmp table or table variables instead this)


if you get comma separated string in SQL , first we need to split theses vales and strore in table(same as list), for this purpose you can use the common function that i provided in my first comment

I am not recommended to use Cursor in sql server , it has some performance issue. if you still want use cursor based looping , try the code below

DECLARE @Code varchar(1000)
DECLARE my_cursor CURSOR FOR  select id from [dbo].[fnSplitterforString]('d1,d2,d3',',')
 
OPEN my_cursor
FETCH NEXT FROM my_cursor INTO @Code
 
WHILE @@FETCH_STATUS = 0
BEGIN
	PRINT @Code -- DO upade here
  
	FETCH NEXT FROM my_cursor INTO @Code
END 
 
CLOSE my_cursor
DEALLOCATE my_cursor

Open in new window

0
harumbAuthor Commented:
That works but I get this error:

Msg 208, Level 16, State 1, Line 2
Invalid object name 'dbo.fnSplitterforString'.

I am using MS Sql 2005 and also Sql 2000, is this a function I need to install or should it be there?
0
BinuthCommented:
here i created the function under "dbo" schema, that's why i called the function dbo.fnSplitterforString....

you can call the function <yourSchema>.fnSplitterforString
0
jayruizCommented:
Are you going to pass the argument 'data1,data2,data3' as a single string (comma-separated value) or individually?
0
jayruizCommented:
and also, does your  'something = something'  also varies?' or you are always using the same where condition. Are you also updating  the same table everytime, I mean for each element of your array?
0
harumbAuthor Commented:
Jayruiz,

Answer to your first question is a separated string "data1, data2, data3' but also I have to use a different delimiter probable a space instead of comma.  The values I am passing in may have a comma so my string would look like this using a space as a delimiter: 'data1 data2,data3 data4 data5'.

In answer to your second question the something=something would vary, I have that part figured out its just this error that I am still not sure about:

Msg 208, Level 16, State 1, Line 2
Invalid object name 'dbo.fnSplitterforString'.

Binuth,

How do I create this fnSplitterforString function, thats not a distributed function with MS Sql?

Thanks.

0
harumbAuthor Commented:
Binuth,

sorry I must be asleep at the wheel here, I see the create function for the 'dbo.fnSplitterforString' in your comments above.  does this function get created dynamically or can I create and store it for later use?  I hope I am asking that write:

CREATE FUNCTION [dbo].[fnSplitterforString]
(
         @IDs Varchar(8000)
        ,@chrDelimiter CHAR(1)
)

Thanks.
0
harumbAuthor Commented:
Binuth,

I just tried this:

CREATE FUNCTION [dbo].[fnSplitterforString]
(
         @IDs Varchar(8000)
        ,@chrDelimiter CHAR(1)
)  

DECLARE @Code varchar(1000)
DECLARE my_cursor CURSOR FOR  select id from [dbo].[fnSplitterforString]('d1,d2,d3',',')
 
OPEN my_cursor
FETCH NEXT FROM my_cursor INTO @Code
 
WHILE @@FETCH_STATUS = 0
BEGIN
        PRINT @Code -- DO upade here
 
        FETCH NEXT FROM my_cursor INTO @Code
END
 
CLOSE my_cursor
DEALLOCATE my_cursor

I am getting a declare error from my query analyzer:
Msg 156, Level 15, State 1, Procedure fnSplitterforString, Line 7
Incorrect syntax near the keyword 'DECLARE'.

Also if I want the list to be delimited by a space rather than a l like you showed me woud I just enter a space or do I need double quotes around the space, like below:

CREATE FUNCTION [dbo].[fnSplitterforString]
(
         @IDs Varchar(8000)
        ,@chrDelimiter CHAR(" ")
)
0
BinuthCommented:
you just replace "[dbo]" with [yourSchema]

like...
CREATE FUNCTION [yourSchema].[fnSplitterforString]

and execute the query then call the same function using  ..... select * from <yourSchema>.fnSplitterforString('data1,data2',',')
CREATE FUNCTION [dbo].[fnSplitterforString] 
(
         @IDs Varchar(8000) 
        ,@chrDelimiter CHAR(1)
)  
RETURNS @Tbl_IDs Table  (RowNum INT,ID VARCHAR(1000))  AS  
BEGIN 
         
         SET @IDs =  @IDs + @chrDelimiter
         
         DECLARE @Pos1 INT
         DECLARE @pos2 INT
         DECLARE @RowNum INT
         
         
         SET @Pos1=1
         SET @Pos2=1
         SET @RowNum = 1        
         WHILE @Pos1<Len(@IDs)
         BEGIN
        
                  SET @Pos1 = CharIndex(@chrDelimiter,@IDs,@Pos1)
                  INSERT @Tbl_IDs SELECT  @RowNum,Cast(Substring(@IDs,@Pos2,@Pos1-@Pos2) AS VARCHAR(1000))
                  SET @Pos2=@Pos1+1
                  SET @Pos1 = @Pos1+1
                  SET @RowNum = @RowNum + 1     
         END 
         RETURN
End

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jayruizCommented:
If you are a member of the dbo group, you can paste the code provided by Binuth to create the function. You have to execute it separately.

CREATE FUNCTION [dbo].[fnSplitterforString]
(
         @IDs Varchar(8000)
        ,@chrDelimiter CHAR(1)
)
 
DECLARE @Code varchar(1000)
DECLARE my_cursor CURSOR FOR  select id from [dbo].[fnSplitterforString]('d1,d2,d3',',')
 OPEN my_cursor
FETCH NEXT FROM my_cursor INTO @Code
 WHILE @@FETCH_STATUS = 0
BEGIN
        PRINT @Code -- DO upade here
          FETCH NEXT FROM my_cursor INTO @Code
END
 CLOSE my_cursor
DEALLOCATE my_cursor








After executing this script, a new function will be registered to your database. You can then use this way:


DECLARE @Code varchar(1000)
DECLARE my_cursor CURSOR FOR  select id from [dbo].[fnSplitterforString]('d1,d2,d3',',')
 OPEN my_cursor
FETCH NEXT FROM my_cursor INTO @Code
 WHILE @@FETCH_STATUS = 0
BEGIN
        PRINT @Code -- DO upade here
          FETCH NEXT FROM my_cursor INTO @Code
END
 CLOSE my_cursor
DEALLOCATE my_cursor




I hope the explanation will help you. You can do that!







0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.