Stucking Stored Procedure for records >300

[samvb]

0
down vote
favorite


I am having a bit of a problem in SS 2008. I have a front-end form that sends item type and quantity. Then SS would produce IDS that equal the number of the given quantity for the given items. So the input is like:

Laptop 1000 UPS 10

The above wouldnt work cos laptops are 1000. But the following works:

laptop 300 ups 350 desktop 300 calculator 300 ... the sum can go beyound 1000 totally as long as the individual items are below 350

Portion of the Code I have is:

DECLARE @ids varchar(max)=''
DECLARE @currentid varchar(15)
DECLARE @begin int=0
DECLARE @intgenwhat smallint -- GENERATE A NUMBER OR A LETTER?
DECLARE @currentidlength smallint

DECLARE @ownercode varchar(3)='ABC'

BEGIN
SET
while @begin<@p1
BEGIN
SET @currentid=''
set @currentidlength=LEN(@currentid + @callercode)
      while @currentidlength<15
        BEGIN
        SET @intgenwhat=FLOOR(2 * rand())
             if @intgenwhat=0
              SET @currentid= @currentid + cast(char(FLOOR(10 * rand()) + 48 ) as varchar)
              else
                SET @currentid= @currentid + cast(char(FLOOR(10 * rand()) + 65) as varchar)

        SET @currentidlength=@currentidlength + 1

        END 
        SET @ids=@ids + @ownercode + @callercode +  @currentid + ','





        SET @begin=@begin + 1
END 

--remove last ,
SET @ids=dbo.DSSMSFN_strRemoveCharacters(@ids,1,len(@ids)-1)

END
SELECT @companyids= @ids

It works as expected. It returns the needed IDs in CSV. But if quantity of a single type is more than 350, it stucks and returns nothing. The sum of single types can be greater than 1000 even and its result is as expected.

Is it a timeout issue? I cant understand whats happening at all since the total quantity can be higher and it works but fails if a single

[computerfox]

I don't quite understand the issue.
This is normal and that's why sites with a large amount of content tend to have multiple pages or a page with a start and end index. What you can do is go to your SQL Server manager and see how long it took for it to run the same query. By the way, if you're dealing with a large amount of content and have a front-end, I would actually consider MySQL since it's built just for that.

[samvb]

thanks...poor english. it is not to display records...it is to insert records. I neeed to generate ID numbers for say 500 computers and insert each into table.

[Rabbit]

What you say you're doing and the code you posted don't match up. There's nothing in there that inserts a record. You have variables that aren't declared. And you call user defined functions that we have no idea what they do. With all this missing information, it's hard to diagnose your issue.

[samvb]

I posted the SP that is called by the inserting SP. The above SP just generates CSV id numbers which are processed by the calling SP that inserts the records into the table (not shown here). Anyway...it is now solved.

[samvb]

btw, how do i close a topic?

[Rabbit]

There's no need to. But can you post your solution in case someone runs into the same problem you had?