Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

How to loop in SQL from list

Posted on 2008-11-08
15
Medium Priority
?
2,050 Views
Last Modified: 2012-06-27
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
0
Comment
Question by:harumb
  • 7
  • 5
  • 3
15 Comments
 
LVL 14

Expert Comment

by:Binuth
ID: 22911402
" loop through a list in sql "
could you explain more ?
0
 

Author Comment

by:harumb
ID: 22911420
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
 
LVL 14

Expert Comment

by:Binuth
ID: 22911461
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:harumb
ID: 22911483
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
 

Author Comment

by:harumb
ID: 22911507
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
 
LVL 14

Expert Comment

by:Binuth
ID: 22911539
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
 

Author Comment

by:harumb
ID: 22913525
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
 
LVL 14

Expert Comment

by:Binuth
ID: 22919150
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
 

Expert Comment

by:jayruiz
ID: 22919733
Are you going to pass the argument 'data1,data2,data3' as a single string (comma-separated value) or individually?
0
 

Expert Comment

by:jayruiz
ID: 22919773
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
 

Author Comment

by:harumb
ID: 22922472
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
 

Author Comment

by:harumb
ID: 22922640
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
 

Author Comment

by:harumb
ID: 22924049
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
 
LVL 14

Accepted Solution

by:
Binuth earned 2000 total points
ID: 22927638
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
 

Expert Comment

by:jayruiz
ID: 22928109
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

810 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