By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,727 Members | 1,396 Online
Bytes IT Community
+ Ask a Question
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
  1.  
  2. DECLARE @ids varchar(max)=''
  3. DECLARE @currentid varchar(15)
  4. DECLARE @begin int=0
  5. DECLARE @intgenwhat smallint -- GENERATE A NUMBER OR A LETTER?
  6. DECLARE @currentidlength smallint
  7.  
  8. DECLARE @ownercode varchar(3)='ABC'
  9.  
  10. BEGIN
  11. SET
  12. while @begin<@p1
  13. BEGIN
  14. SET @currentid=''
  15. set @currentidlength=LEN(@currentid + @callercode)
  16.       while @currentidlength<15
  17.         BEGIN
  18.         SET @intgenwhat=FLOOR(2 * rand())
  19.              if @intgenwhat=0
  20.               SET @currentid= @currentid + cast(char(FLOOR(10 * rand()) + 48 ) as varchar)
  21.               else
  22.                 SET @currentid= @currentid + cast(char(FLOOR(10 * rand()) + 65) as varchar)
  23.  
  24.         SET @currentidlength=@currentidlength + 1
  25.  
  26.         END 
  27.         SET @ids=@ids + @ownercode + @callercode +  @currentid + ','
  28.  
  29.  
  30.  
  31.  
  32.  
  33.         SET @begin=@begin + 1
  34. END 
  35.  
  36. --remove last ,
  37. SET @ids=dbo.DSSMSFN_strRemoveCharacters(@ids,1,len(@ids)-1)
  38.  
  39. END
  40. SELECT @companyids= @ids
  41.  
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
Share this Question
Share on Google+
6 Replies


computerfox
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

Rabbit
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

Rabbit
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

Post your reply

Sign in to post your reply or Sign up for a free account.