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

SQL outer loop

I have a table with a field that needs to be parsed.  The problem that I am having is that it is possible for each user (the main selection criteria of the query) may have multiple records with the parse requirements.  I am trying to handle this with the below code.  I belive that it is commented well enought to follow.  

The outer loop is running the correct amount of times, but the inner loop is only executing on loop #1.

 

/*****************************************
THIS IS JUST TO CREATE A TEST SAMPLE
*****************************************/
create table #security 
(dept varchar(100)
,division varchar(100)
)
 
 
-- Situation where a user has only specific divisions for a department
Insert into #security
(dept,division)
Values
('10','1001,1003,1005')
 
Insert into #security
(dept,division)
Values
('20','2000,2201')
 
Insert into #security
(dept,division)
Values
('55','5500,5501')
 
Insert into #security
(dept,division)
Values
('70','7000,7001')
 
-- User has access to ALL divisions of a department
Insert into #security
(dept,division)
Values
('60','%')
 
Insert into #security
(dept,division)
Values
('45','%')
 
 
 
/*****************************************
*****************************************/
 
-- First select the departments where the division is % 
-- Bounce the department up against clarity dep to get the divisions
select distinct dept,gl_prefix 
INTO #security2
from clarity_dep 
inner join #security on dept = rpt_grp_six 
where division = '%' 
 
-- Determine how many records have division restrictions
Select count(division) as Loop_Count
Into #OuterLoops
From #Security
Where division <> '%'
 
 
-- next get all the departments where the division is NOT % add those divisions to the temp table
-- multiple divisions can be written on one line.  
declare @divs varchar(200)
set @divs = (select top 1 division from #security where division <> '%')
declare @loops int
set @loops = (Select Loop_Count From #OuterLoops)
declare @start int
set @start = 1
declare @count int
set @count = 1
 
 
-- This splits out the multiple division scenarios (i.e. 1005,1001,1003) and places them in the scurity
-- table 
While @count <= @loops
	Begin	
		While @start <= len(@divs)
			Begin	
				Insert into #security2 (dept,gl_prefix)
				Values
				(left(@divs,2),substring(@divs,@start,4))				
				set @start = @start + 5	
		END
 
	Delete  from #security where division = @divs
	Set @count = @count + 1
End
 
 
select * from #security2

Open in new window

0
gdspeare
Asked:
gdspeare
  • 4
  • 4
1 Solution
 
brad2575Commented:
your never resetting your start variable after each loop

code fixed below

While @count <= @loops
        Begin   
              --reset start variable  for every loop
             set @start = 1
 
                While @start <= len(@divs)
                        Begin   
                                Insert into #security2 (dept,gl_prefix)
                                Values
                                (left(@divs,2),substring(@divs,@start,4))                    
                              --setting it here but the next loop it is never reset
                                set @start = @start + 5 
                END
 
        Delete  from #security where division = @divs
        Set @count = @count + 1
End

Open in new window

0
 
gdspeareAuthor Commented:
Thanks for responding Bran, but that didn't do it.  

I don't want to reset my variable @start b/c this variable is used to parse out itmes from a single record. For example if my field value is: 1001,1003,1005,1009, my variable @start states on loop 1 begin at position 1 and take the next 4 items, on loop 2 begin at position 5 and take the next 4 items, etc.

0
 
gdspeareAuthor Commented:
Actually Brad (sorry about "Brian" earlier) I understand what you mean about resetting the @start.  

It still didn't work the way I wanted, but I see how I do need to get that done.

David
0
Independent Software Vendors: 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!

 
brad2575Commented:
I understand you want to loop throughthe @start for every item in @divs.  

Are you going to be looping through multiple @divs values?  Or just one?  It sounds like multiple but your code is only set up for one?

If you are only doing one @divs you should not need the nested loops.  If you are doing multiple @divs I do not see where you are getting the second value for @divs plus you will need to reset the @start variable for each new div.

0
 
gdspeareAuthor Commented:
I'm looping through possibly multiple @divs.  The count of how many times I'll be looping is held in the #outerloops table.

How do you see an improvement in the sql to account for multiple @divs loops
0
 
brad2575Commented:
You can use a curser, I think this recreates everything you had above (without the temp tables and without most of the variables).  If you need me to explain any of it let me know.  You WILL have to update to be the real tables you are using (or declare the temp tables and add them to the code).


	DECLARE @dept as varchar(500)
	DECLARE @divs as varchar(5000)
	declare @start int
	set @start = 1
 
	DECLARE @MyCursor as CURSOR
	SET @MyCursor = CURSOR FAST_FORWARD
	FOR
 
	-- select query here with joins/etc to get DIV data AND securty
    SELECT dept, div
	From tableName
	Where (whereclausehere)
	
 
 
	-- puts the data into the curser for the above select 
	OPEN @MyCursor
	FETCH NEXT FROM @MyCursor
	INTO @dept, @divs
 
	-- loops through all the recrods retruend
	WHILE @@FETCH_STATUS = 0
	BEGIN
		
		-- reset for every div data
		set @start = 1
 
		-- loop through DIV data to insert into table
		While @start <= len(@divs)
            Begin   
                Insert into #security2 (dept, gl_prefix)
                Values(left(@divs,2),substring(@divs,@start,4))                               
                set @start = @start + 5 
			END
 
 
		-- *********** end loop stuff ****************************************************************
		-- gets the next recrods in the curser
		FETCH NEXT FROM @MyCursor
		INTO @TableName
	END
 
	-- close curser 
	CLOSE @MyCursor
	DEALLOCATE @MyCursor

Open in new window

0
 
gdspeareAuthor Commented:
A cursor does seem like a better way, but I have never done one.


I'm getting a Must declare the scalar variable "@security2" which is at the "end of loop stuff" section.

?

DECLARE @dept as varchar(500)
	DECLARE @divs as varchar(5000)
	declare @start int
	set @start = 1
 
	DECLARE @MyCursor as CURSOR
	SET @MyCursor = CURSOR FAST_FORWARD
	FOR
 
	-- select query here with joins/etc to get DIV data AND securty
    SELECT dept, division
	From #security
	Where division <> '%'
	
 
 
	-- puts the data into the curser for the above select 
	OPEN @MyCursor
	FETCH NEXT FROM @MyCursor
	INTO @dept, @divs
 
	-- loops through all the recrods returned
	WHILE @@FETCH_STATUS = 0
	BEGIN
		
		-- reset for every div data
		set @start = 1
 
		-- loop through DIV data to insert into table
		While @start <= len(@divs)
            Begin   
                Insert into #security2 (dept, gl_prefix)
                Values(left(@divs,2),substring(@divs,@start,4))                               
                set @start = @start + 5 
			END
 
 
		-- *********** end loop stuff ****************************************************************
		-- gets the next records in the curser
		FETCH NEXT FROM @MyCursor
		INTO @security2
	END
 
	-- close curser 
	CLOSE @MyCursor
	DEALLOCATE @MyCursor
 
--Open in New Window

Open in new window

0
 
brad2575Commented:
sorry, this code, I missed updating the last part:
-- *********** end loop stuff ****************************************************************
                -- gets the next records in the curser
                FETCH NEXT FROM @MyCursor
                INTO @security2
 
 
would be replaced with 
-- *********** end loop stuff ****************************************************************
                -- gets the next records in the curser
                FETCH NEXT FROM @MyCursor
                INTO @dept, @divs

Open in new window

0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

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