SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Enthusiast
    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

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    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?

  4. #4
    SitePoint Wizard Ren's Avatar
    Join Date
    Aug 2003
    Location
    UK
    Posts
    1,060
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Tried initialising @outFLNums to an empty string ('') before entering the loop?

  5. #5
    SitePoint Enthusiast
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •