CREATE PROCEDURE dbo.ap_get_pattern_from_partnumber 

@partnumber varchar(50)

AS

Begin 

set nocount on 

create table #temp (pattern_code varchar(6), block_index int, value_code varchar(20), value_index int) 
create table #temp2 (pattern_code varchar(6))

insert into #temp
	SELECT  pattern_code, block_index, value_code, value_index
	FROM   sdb_block_value a
	join 
		sdb_pattern_block b
	on 
		a.block_id=b.block_id
		and 
		pattern_code in ('2139', '2170', '2172', '2186')
	order by block_index, value_index

--Grab distinct pattern codes
Declare cursID cursor for 
	select distinct(pattern_code) from #temp 

Declare @curPattern varchar(6),
	@isMatch_Value bit, 
	@isMatch_Block bit, 
	@isMatch_Pattern bit,
	@curPartNumber varchar(50)

set @isMatch_Value=0
set @isMatch_Block=0
set @isMatch_Pattern=0

--Begin First Loop
open cursID 
fetch next from cursid into @curPattern
while (@@fetch_status<>-1) 
Begin 
	
	Declare Cursid2 cursor for 
		select block_index from #temp 
		where pattern_code=@curPattern
	
	Declare @curIndex int, 
		@curmatch varchar, 
		@minlength int
	
	set @curmatch=''
	set @minlength=0
	
	--Begin Second Loop
	open cursid2
	fetch next from cursid2 into @curIndex
	while (@@fetch_status<>-1) 
	Begin 

		Declare cursid3 cursor for 
			select value_index from #temp
			where pattern_code=@curPattern
			and block_index=@curIndex

		Declare @curvIndex int		

		--Begin Third loop
		Open cursid3
		fetch next from cursid3 into @curVindex
		while (@@fetch_status<>-1) 
		Begin 
			select @minlength=len(value_code)
			from #temp 
			where pattern_code=@curPattern
			and block_index=@curIndex
			and value_index
	
			select @curPartNumber=@curPartNumber + value_code 
			from #temp 
			where pattern_code=@curpattern
			and block_index=@curIndex
			and value_index=@curVindex

			if @curPartNumber=substring(@partnumber, 0, len(@curPartNumber)) 
				begin 
					set @isMatch_Value=1
				end 

			if @isMatch_value=1
				Begin 
					set @isMatch_value=0
					fetch next from cursid3 into @curVindex
					set @isMatch_Block=1
				end 
			else 
				begin 
					set @isMatch_value=0
					set @isMatch_Block=0
					set @curPartNumber=''
				end 

		end 
		deallocate cursid3

		if @isMatch_block=1 
			Begin 
				set @isMatch_Pattern=1
				fetch next from cursid2 into @curIndex
			end 
		else 
			Begin 
				set isMatch_pattern=0
				set isMatch_block=0
			end 

	end
	deallocate cursid2
	
	if @isMatch_Pattern=1 
		begin 
			insert into #temp2 (@curPattern)
		end 

fetch next from cursid into @curPattern
end 
deallocate cursid



End

quote:

---

Originally posted by nr
set isMatch_pattern=0
set isMatch_block=0
s.b.
set @isMatch_pattern=0
set @isMatch_block=0


insert into #temp2 (@curPattern)
s.b.
insert into #temp2 select @curPattern

---

quote:

---

Originally posted by ChrisFretwell

When I try to compile, it gives the select error as on this line (line 80)

Indicating that its not ready for a select following the previous statement. So, looking at the previous statement, you have a hanging where
select @minlength=len(value_code)
from #temp
where pattern_code=@curPattern
and block_index=@curIndex
and value_index

and value_index what??? Fix this and all should be well.

---