procedure take extremely too long to execute!!! pls Help

**nellyihu** · Jan 18, 2006 06:55

hi guys
i wrote a strored procedure in mssql that is supposed to scan thru a table get the last number based on the query and the genearte unique numbers serially in this format "GAL/001,GAL/002,GAl/003........" depending on the number of count returned from the query and i'm supposed to return it as a csv to an asp page as an output parameter but the procedure takes eternity to execute and doesnt even execute but rather times out pls help what i'm i doin wrong? i need a reply urgently
this is the procedure:

Code:

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


ALTER   procedure dbo.STP_SUB_DIVIDE_FILE
(
	@PropertyTypeId	int,
	@FileNumber			varchar(50),
	@DivNum				int,
	@outFLNums			varchar(8000) output

)

as
--first do a search if that file number exists
declare @counter int,@x int,@xx int
declare @pre varchar(3),@proppre varchar(5),@filenum varchar(20),@xxx varchar(1)
set @counter=1
select @proppre=ShortName from dbo.[Propertytype] where PropertyTypeId=@PropertyTypeId
select @x=count(PropertyTypeId) from dbo.[property]where PropertyTypeId=@PropertyTypeId --get no of people with this id
set @xx=@x
if len(@xx)=1
	begin
		set @pre='00'
	end
if len(@xx)=2
	begin
		set @pre='0'
	end	
while (@counter <= @DivNum)
	begin
		set @xx=@xx+1 --continue from where the last number stop and increament		
		--build a csv to return
		if @counter=@DivNum
			begin
				set @filenum=@proppre + '/'+ @pre + cast(@xxx as varchar(3))  --file number
				set @outFLNums=@outFLNums + @filenum  --send out witout trail comma
			end
		else
			begin
				set @filenum=@proppre + '/'+ @pre + cast(@xxx as varchar(3)) --file number
				set @outFLNums=@outFLNums + @filenum + ','
			end		
	end
return



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO