SitePoint Sponsor |
|
User Tag List
Results 1 to 5 of 5
-
Jan 18, 2006, 06:55 #1
- Join Date
- Jun 2005
- Posts
- 74
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
procedure take extremely too long to execute!!! pls Help
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
-
Jan 18, 2006, 07:03 #2
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
while (@counter <= @DivNum)
you never change either of these values inside the loop, so it just loops forever
-
Jan 18, 2006, 07:54 #3
- Join Date
- Jun 2005
- Posts
- 74
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
thanks but the csv does not build it retutns null any ideas?
-
Jan 18, 2006, 08:09 #4
Tried initialising @outFLNums to an empty string ('') before entering the loop?
-
Jan 18, 2006, 08:22 #5
- Join Date
- Jun 2005
- Posts
- 74
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
thanks man it really worked fine very greatful.
keep up the good work
Bookmarks