Stored Procedure didn't run all the way when executed from Alpha

[Reynaldi]

Hi! so basically my problem is that I made a new stored procedure without passing any variable and tested it by directly executing it from sql. It runs perfectly, so the next thing I tried is running it on xbasic from alpha, but when I checked the database, it seems only part of the data was processed while when I debugged the code from alpha the callresult.text says "success".

My SP is used to process data from table A and insert it from table B, first I created a new table Temp and inserted data from table A to Temp, do a while loop based on this Temp table and inside the loop I collected the data necessary from another table, stored it in a variable and inserted it to table B and lastly update a column in both table A and Temp called "ProcessedStatus" from 0 to 1.

What I noticed was, first, when I executed the SP from sql, all 1068 rows of data got the column ProcessedStatus updated from 0 to 1. But when I executed it from xbasic, only around 80-300 rows for affected. I didn't pass any parameter to this SP since all the data I need is from another table so I don't think the problem lies on the data processed and no error showed up when I executed it from sql directly.
Secondly, I have another SP that has the same function to the one I said before, process data from a table and inserted it to another. But the message shown in sql is different. Where my SP in question shows

(1068 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

and so on until the loop ends (1068 times), where the other shows

Command(s) completed successfully.

is this have anything to do with why my SP didn't run all the way?

Thirdly, my xbasic code runs 3 SP in the same script, where 2 of them, let's call them SP_A and SP_B are the one that returns "Command(s) completed successfully." and there's a few seconds time lag when I'm at the line where the SP got executed, and the third one, SP_C is the SP that didn't run all the way, when I wen't over the line that executed the SP, I got a little to no time lag. So I didn't think this problem was because a timed out from alpha.

And lastly I encounter the same problem when I was developing SP_A and SP_B where they won't complete all the way from alpha, and I asked about it too in this forum. A few people says it's because I used cursor to loop which didn't have a good performance, and they advised me to use temporary table. And so I did and my problem was solved. But now I'm encountering the same problem while I'm using temporary table.

And I got stuck around this problem for the past few days, is there any way to solve this? And what is the reasons behind this problem?

[lvasic]

Re: Stored Procedure didn't run all the way when executed from Alpha

I run stored procedures all the time without these issues. Can you run 1 stored procedure and have that procedure call the other stored procedures? Also, it seems like you added a temp table but still have a loop statement. I remember using loops in SQL about twenty years ago when I first came from ISAM files, it would be interesting to see why you need a loop.

[RichCPT]

Re: Stored Procedure didn't run all the way when executed from Alpha

At the top of your SQL procedures put:

SET NOCOUNT ON;

Perhaps that will fix the issue.

[Pat Bremkamp]

Re: Stored Procedure didn't run all the way when executed from Alpha

I use cursors in a number of stored procedures and have no problem with them. In fact last night I ran one on a table with 1.2 million records and it processed successfully in about 16 hours. So I don't think cursor versus temp table is the problem.

I think your problem is related to the 1068 responses. If those responses are feeding back to Alpha in a result set, I can see Alpha choking on them.

I have another SP where I use Lee's suggestion of a master SP that calls the other SP's gathering data from 6 different tables, formatting it and inserting it into another (memory engine) table. This also works fine, but what I learned in that process was:
Where you can, create your sub SPs as functions instead of stored procedures.
If you must use procedures, then don't feed back results. Instead check the results in the table or use counters in the SP and feed back a single result.

[Reynaldi]

Re: Stored Procedure didn't run all the way when executed from Alpha

Ivasic : I did with SP_A and SP_B but when i got that error before, i split it for alpha to call 2 SP instead, when my problem was solved I didn't merge them back in worry it would interfere my solved problem. I loop based on the temp table because I want to do a process for each row, the first thing I tried was

while @id < @lastid
begin
..........
set @id = @id +1
end

but then I think I can't use that since I can't be sure the ID is always previous ID+1, so I created another table and insert data to it to get incremental ID from 1 to last ID = total rows and do while like I wrote above


RichCPT : Oh wow haha It works! I never knew what this line was for so I didn't bother to use it, Thank you!


Pat Bremkamp : And it seems those 1068 responses really is the culprit, when I used Rich's suggestion it ran all the way from alpha.
I didn't knew how to create a function yet, but I'll keep that in mind to create function instead of SP next time

[lvasic]

Re: Stored Procedure didn't run all the way when executed from Alpha

Have you tried using ROW_NUMBER()?