August 26, 2008 at 9:22 am
I am having an issue with a server that we are going to be moving to new hardware running SQL 2005 in 8.0 compatibility mode from actual SQL 2000. We have a stored procedure that is vital to the front-end app that is not acting as expected under SQL 2K5. by that I mean it is returning no rows while it returns the expected results in the current production system. The code is identical based on comparison tool and visual review. Let me preface this with the disclaimer that I know there are several things wrong with the code, it is ex-vendor supplied for one, which is part of the problem. Here is the stored procedure code, any help is greatly apprciated as this is holding up any further testing.
CREATE PROCEDURE [dbo].[GetAvailableProductsQuantityList](
@ProductID bigint = null,
@SKU varchar(50) = null,
@CustomerID bigint = null,
@userid bigint = null,
@OrderID bigint = null,
@WarehouseID bigint = null
)
AS
BEGIN
declare @Type int
if (@ProductID is null)
BEGIN
select @Type = TYPE_ID, @ProductID = PRODUCT_ID from PRODUCT where SKU_NO = @SKU and CUSTOMER_ID = @CustomerID
END
else
BEGIN
select @Type = TYPE_ID, @CustomerID = CUSTOMER_ID from PRODUCT where PRODUCT_ID = @ProductID
END
declare @UserCustomerID bigint
select @UserCustomerID = CUSTOMER_ID from USERS where [USER_ID] = @userid
create table #T (WID bigint, QTY int, Iter numeric(5,0) Identity not null)
if (@UserCustomerID = -1)
begin
insert #T
SELECT W.WAREHOUSE_ID, WP.QUANTITY_AVAILABLE
FROM WAREHOUSE W, CR_CUSTOMER_WAREHOUSE CW, CR_WAREHOUSE_PRODUCT WP
where W.WAREHOUSE_ID=CW.WAREHOUSE_ID
AND (@CustomerID < 0 or CW.CUSTOMER_ID=@CustomerID)
and WP.WAREHOUSE_ID = W.WAREHOUSE_ID
and WP.PRODUCT_ID = @ProductID
and (@WarehouseID is null or WP.WAREHOUSE_ID = @WarehouseID)
end
else
begin
if exists (select 1 from CR_USER_WAREHOUSE where USER_ID = @userid)
begin
insert #T
SELECT W.WAREHOUSE_ID, WP.QUANTITY_AVAILABLE
FROM WAREHOUSE W, CR_USER_WAREHOUSE CW, CR_WAREHOUSE_PRODUCT WP
where W.WAREHOUSE_ID = CW.WAREHOUSE_ID
AND CW.[USER_ID] = @userid
and WP.WAREHOUSE_ID = W.WAREHOUSE_ID
and WP.PRODUCT_ID = @ProductID
and (@WarehouseID is null or WP.WAREHOUSE_ID = @WarehouseID)
end
else
begin
insert #T
SELECT W.WAREHOUSE_ID, WP.QUANTITY_AVAILABLE
FROM WAREHOUSE W, CR_CUSTOMER_WAREHOUSE CW, CR_WAREHOUSE_PRODUCT WP
where W.WAREHOUSE_ID=CW.WAREHOUSE_ID
AND CW.CUSTOMER_ID=@CustomerID
and WP.WAREHOUSE_ID = W.WAREHOUSE_ID
and WP.PRODUCT_ID = @ProductID
and (@WarehouseID is null or WP.WAREHOUSE_ID = @WarehouseID)
end
end
if (@Type > 0) -- All Tracked
begin
select I.ITEM_ID, I.WAREHOUSE_ID, I.STATUS, I.IS_ONHOLD, P.SKU_NO, P.TYPE_ID, I.SERIAL_NO, I.CUSTOMER_ORD_NO, I.LOT_NO, I.LOT_EXP_DATE, I.HOLD_REASON_ID, I.QUANTITY, P.PRODUCT_ID
from ITEM I
inner join RECEIPT R on (I.RECEIPT_ID = R.RECEIPT_ID and R.FINALIZED = 1)
-- additional checking, since v2121/v307
inner join PRODUCT P on (I.PRODUCT_ID = P.PRODUCT_ID and P.PRODUCT_ID = @ProductID )
where
I.STATUS = 2
and I.IS_ONHOLD = 0
and I.ORDER_ID is null
and exists (select 1 from #T where #T.WID = I.WAREHOUSE_ID)
and (@WarehouseID is null or I.WAREHOUSE_ID = @WarehouseID)
and I.QUANTITY > 0 --by Nune
end
else
begin
select W.[NAME] + ' (' + CONVERT(VARCHAR,(QTY - sum(isnull(QUANTITY,0)))) + ' Available)' as WAREHOUSE_NAME, WID as WAREHOUSE_ID
from #T, PICK P, WAREHOUSE W
where exists (select 1 from [ORDER] O where P.ORDER_ID = O.ORDER_ID and O.ORDER_STATUS = 1 and (@OrderID is null or P.ORDER_ID != @OrderID)) -- by client request
and P.PRODUCT_ID = @ProductID
and P.WAREHOUSE_ID =* #T.WID
and W.WAREHOUSE_ID = WID
group by W.[NAME], WID, QTY
having QTY - sum(isnull(QUANTITY,0))> 0
Order by WAREHOUSE_NAME
end
END
And there it is. As I said, in production, it returns expected results and no rows from the SQL 2005 version of the exact same database. Couple of notes, deprecated join syntax (=*) and use of system reserved names have been recognized (type_id and user_id), again so much of a mess it is hard to see where to start fixing it and after looking at it all day yesterday and this morning I am stumped. did I mention help was greatly appreciated? Thanks.
Scott
August 26, 2008 at 9:45 am
Could you provide the DDL (create statements) for the table, some sample data for the tables (in the form of insert statements that can be cut, paste, and run to load the tables), and the expected results from the procedure based on the sample data?
This will help us help you.
This article will help you if you need more info: http://qa.sqlservercentral.com/articles/Best+Practices/61537/
😎
September 2, 2008 at 6:26 am
Lynn-
I appreciate the advice and the help you offered on this topic, after further review it seems to be a known, old but known issue with the difference between the '=*' join syntax and where the WHERE clause is applied, found 2 articles that seems to point to the issue I am having.
This was the clearer of the articles to me. Thanks again.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply