Posted by Erland Sommarskog on 06/04/07 21:29

Ben (pillars4@sbcglobal.net) writes:
> .Execute ' This is where it hangs up...
>
> TotalItems = .Parameters("@TotalInStock")
> TotalCost = .Parameters("@TotalCost")
>
> End With
>
> Set com = Nothing
>
> and the store procedure is:
>
> CREATE PROCEDURE DBO.sp_Recalculate

Do not use the sp_ prefix in your procedures. This prefix is reserved
for system procedures, and SQL Server will first look for these in master.
I don't think this explains why your process hangs, but I nevertheless
wanted to point it out.

> @ItemNumber nvarchar(50),
> @TotalInStock int = 0,
> @TotalCost money = 0
> AS

Judging from the code, the parameters @TotalInStock and @TotalCost
should be declared as OUTPUT. Right now your procedure is only a no-op.

Also, I can't see in you code that you create these parameters when you
call the procedure. You need to do that; you cannot just refer the
parameters after the call.

> BEGIN
> SET @TotalInStock = (
> SELECT Sum([Quantity in Stock])
> FROM [Inventory Products]
> WHERE [Item Number] = @ItemNumber)
>
> SET @TotalCost = (
> SELECT Sum([Cost] * [Quantity in Stock])
> FROM [Inventory Products]
> WHERE [Item Number] = @ItemNumber)
>
> END

Rewrite as

SELECT @TotalInStock = Sum([Quantity in Stock]),
@TotalCost = Sum([Cost] * [Quantity in Stock])
FROM [Inventory Products]
WHERE [Item Number] = @ItemNumber

That will slash the execution time in half.

> When the process goes to the ".Execute" line, it hangs up for a long time
> then gives me an error message "Everflow". I have been trying to solve
> this issue but do not have an idea for now of the cause.

I guess you mean "Overflow"? That sounds like VB message to me, which
would indicate that you are using the wrong data type for TotalItems.
(Check that you did not mistakenly declare it as Integer.) Then again,
it does not seem that you would get anything back from the procedure
at all. But maybe that is the problem? You get some unintialised junk?

> Below is my finding:
> a. When I run the stored procedure in the SQL analyzer, it works just
> fine.

And it completes in how long time?




--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]