Problem on STORED PROCEDURE - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Problem on STORED PROCEDURE

Author

Topic

mrzcrn
Starting Member

15 Posts

Posted - 2004-03-30 : 06:50:30

Hello everybody

I have a problem on a stored procedure.
My operating system is Windows 2000 and DB is SQL Server 2000

I have 2 tables, one is a temporary table called QUALITY_DATA_LF_TMP and the

other is the target table called QUALITY_DATA_REPORT_LF.
The 2 table have the following structure:
QUALITY_DATA_LF_TMP
COILID nvarchar(10)
LENGTH int
MEA1 int
MEA2 int
:
MEA100 int

QUALITY_DATA_REPORT_LF
COILID nvarchar(10)
TYPE int
MEA1 int
MEA2 int
:
MEA100 int

QUALITY_DATA_LF_TMP have not primary key,
QUALITY_DATA_REPORT_LF have primary key on COILID and TYPE.

A dedicated application developped in C++ save data sampled on length in the

temporary table QUALITY_DATA_LF_TMP (about 5000 records).
N.B.: before to save, the table is completly cleaned.
At the end of the process the application run a stored procedure to compute for

each measure (MEA1, MEA2 ...MEA100) the MIN, MAX, AVG and STDEV and save the

results in the table QUALITY_DATA_REPORT_LF in 4 records defined by the value

of field TYPE.
In the table QUALITY_DATA_REPORT_LF the field TYPE have the following meaning:
TYPE = 1 --> MIN
TYPE = 2 --> MAX
TYPE = 3 --> AVG
TYPE = 4 --> STDEV

The operations done in the stored procedure are the following:

NB: @PIECE_ID is a value passed to the stored procedure.

insert into QUALITY_DATA_REPORT_LF
select @PIECE_ID, 1, MIN(MEA1),MIN(MEA2),....,MIN(100)
from QUALITY_DATA_LF_TMP

insert into QUALITY_DATA_REPORT_LF
select @PIECE_ID, 2, MAX(MEA1),MAX(MEA2),....,MAX(100)
from QUALITY_DATA_LF_TMP

insert into QUALITY_DATA_REPORT_LF
select @PIECE_ID, 3, AVG(MEA1),AVG(MEA2),....,AVG(100)
from QUALITY_DATA_LF_TMP

insert into QUALITY_DATA_REPORT_LF
select @PIECE_ID, 4, STDEV(MEA1),STDEV(MEA2),....,STDEV(100)
from QUALITY_DATA_LF_TMP

Normally everything is working well and the stored procedure compute the

aggregate functions and the table QUALITY_DATA_REPORT_LF is filled well.

After some months of works the stored procedure generate an error when try to

compute the MIN and the MAX.

If the section computing the MIN and the MAX is commented the Stored procedure

works well again.

In order to solve the problem i tryed to compute all the minimum and the

maximum of measures MEA1 ..MEA100 not more one shot all toghether but one by

one, saving each MIN and each MAX in 2*100 local variables. After i do the

insert into QUALITY_DATA_REPORT_LF using the local variables.
Using this stupid modification, that take more time than previous solution, the

stored procedure is working well.

I have this problem only for MIN and MAX aggreate function. For AVG and STDEV

all is working well. That is strange because AVG and STDEV should get more

resources then MIN and MAX.

Someone can understand the problem?

Thanks
maurizio from Italy

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2004-03-30 : 10:10:38

What is the error message do you get when you include the MIN and MAX fucntions in your sproc?

________________
Make love not war!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources