459,727 Members | 1,396 Online
Need help? Post your question and get tips & solutions from a community of 459,727 IT Pros & Developers. It's quick & easy.

Stucking Stored Procedure for records >300

 100+ P: 228 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: Expand|Select|Wrap|Line Numbers   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 Jul 10 '15 #1
6 Replies

 100+ P: 278 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. Jul 11 '15 #2

 100+ P: 228 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. Jul 12 '15 #3

 Expert Mod 10K+ P: 12,430 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. Jul 12 '15 #4

 100+ P: 228 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. Jul 12 '15 #5

 100+ P: 228 btw, how do i close a topic? Jul 12 '15 #6

 Expert Mod 10K+ P: 12,430 There's no need to. But can you post your solution in case someone runs into the same problem you had? Jul 13 '15 #7