Stored Procedure locks up. - 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)

Stored Procedure locks up.

Author

Topic

aclarke
Posting Yak Master

133 Posts

Posted - 2001-06-06 : 13:57:58

I have 2 stored procedures - one on my "quotes" table and one on "orders". They are identical, but my orders one doesn't finish. It puts my CPU up to 100% and just chugs away until I cancel it. Here's the sp:


CREATE PROCEDURE SubordinateSubcategory_Orders
@localOrderID int,
@ParentSKU varchar(50),
@when datetime

AS

if (isDate(@when) = 0) set @when = getDate()

select
 Subordinate.SubordinateSKU, Subordinate.Ratio, Subordinate.GroupID,
 Subcategory.ID as SubcategoryID, Subcategory.CategoryID, Subcategory.ManufacturerID, Subcategory.SubcategoryName, Subcategory.AmountPerContainer, Subcategory.ContainerMeasurement, Subcategory.Container, Subcategory.Note, Subcategory.MoreInfo, Subcategory.Testimonial, Subcategory.Weight, Subcategory.HowToInstall, Subcategory.QuotePageHyperlink, Subcategory.Subordinate, Subcategory.Price AS Subcategory_Price,
 SubordinateGroup.MoreInfoURL, SubordinateGroup.name AS SubordinateGroupName,
 Category.CategoryName,
 Manufacturer.ManufacturerName,
 Orders_LineItem.SubcategoryName AS Main_SubcategoryName, Orders_LineItem.SubcategoryID AS Main_SubcategoryID, Orders_LineItem.Qty AS Main_Qty, Orders_LineItem.SqFt as Main_SqFt,
 SubcategorySKU.Price as SKU_Price, SubcategorySKU.Thumbnail

FROM Orders_LineItem
 LEFT JOIN Subordinate ON Orders_LineItem.SKU = Subordinate.SKU
 LEFT JOIN SubordinateGroup ON Subordinate.GroupID = SubordinateGroup.subordinateGroupID
 LEFT JOIN SubcategorySKU ON Subordinate.SubordinateSKU = SubcategorySKU.SKU
 LEFT JOIN Subcategory ON SubcategorySKU.SubCategoryID = Subcategory.subcategoryID
 LEFT JOIN Manufacturer ON Subcategory.ManufacturerID = Manufacturer.manufacturerID
 LEFT JOIN Category ON Subcategory.CategoryID = Category.categoryID

WHERE Orders_LineItem.OrderID=@LocalOrderID
	and Orders_LineItem.CategoryID<>3
	and Orders_LineItem.Subordinate=0
	and Manufacturer.Live=1
	and Category.Live=1
	and SubcategorySKU.Live=1
	and Orders_LineItem.SKU=@ParentSKU

	and subcategory.time_validFrom <= @when
	and subcategory.time_validTo >= @when
	and subcategorySKU.time_validFrom <= @when
	and subcategorySKU.time_validTo >= @when
	and orders_lineItem.time_validFrom <= @when
	and orders_lineItem.time_validTo >= @when

ORDER BY Subordinate.GroupID, Subcategory.ManufacturerID
GO

The line

if (isDate(@when) = 0) set @when = getDate()

is what's screwing it up. Yesterday I tried defining @when as a varchar and that worked, but today it doesn't work. I do this @when thing in about 50 other stored procedures and it works in them - but this has a lot more where ... <= @when kinds of clauses in it. But the SubordinateSubcategory_Quotes stored procedure works perfectly!!!

I don't get it. Something's not happening in sequence or something, and I need help...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources