Stored Procedure Hangs but works fine as a Select

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 2005 Forums

Transact-SQL (2005)

Stored Procedure Hangs but works fine as a Select

Author

Topic

Badjjl
Starting Member

6 Posts

Posted - 2009-05-15 : 07:09:27

Hi

Please can you help,I have a stored procedure uspCSVBIReturnedItemsOver2Packs when run with the parameters '2009-05-12 00:00:00:000','2009-05-12 23:59:59:997'
as exec uspCSVBIReturnedItemsOver2Packs '2009-05-12 00:00:00:000','2009-05-12 23:59:59:997'it just hangs, but when I execute the same script as a select statement in query analyser with the same parameters it works fine

Would you have any idea why the stored procedure does not work?

Best Regards

Julian

USE [AsosReport]
GO
/****** Object: StoredProcedure [dbo].[uspCSVBIReturnedItemsOver2Packs] Script Date: 05/14/2009 13:21:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- ===================================================================================================================
-- Author: Julian Louisy
-- Create date: 13 May 2009
-- Description: With this information, we can evaluate the customer’s returns and why they may send their goods back
-- in separate packages. This information will be used to refine the returns process. For example, the customer
-- linked to order number 11557396, the customer return was processed on the 05/04/09 and another return was processed
-- on the 07/04/09. We need to understand how many customer return goods from the same order in more than one package.
-- ===================================================================================================================
exec uspCSVBIReturnedItemsOver2Packs '2009-05-12 00:00:00:000','2009-05-12 23:59:59:997'
--
--
ALTER PROCEDURE [dbo].[uspCSVBIReturnedItemsOver2Packs](@StartDate DATETIME,@EndDate DATETIME)
AS

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
--
declare @startdate datetime, @enddate datetime

select @startdate = '2009-05-12 00:00:00:000'
select @enddate = '2009-05-12 23:59:59:997'

SELECT ri.receiptid AS 'Receipt Id'
,ri.shortdescription AS 'Items Return Description'
-- ,CONVERT(VARCHAR(20),ri.Dateentered,103) AS 'Order Date'
-- ,CONVERT(VARCHAR(20),dc.DespatchDate,103) AS 'Date Shipped'
,CONVERT(VARCHAR(20),vi.DateEntered,103) AS 'ReturnProcessDate'
,vr.ReasonName
,pos.PriceIncTax as 'Retail Price'

FROM [dbo].[voiditem] vi WITH (NOLOCK) -- Voiditem table holds all returns & cancellation details

JOIN [dbo].[receiptitem] AS ri WITH (NOLOCK)
ON vi.receiptitemid = ri.receiptitemid
AND vi.DateEntered BETWEEN @StartDate AND DATEADD(s,-1,DATEADD(d,+1,@EndDate)) -- added to ensure enddate is 23:59:59 if not enddate would be cut off at midnight and a days records would be lost

JOIN [dbo].[voidreason] AS vr WITH (NOLOCK)
ON vi.enteredbyreasonid = vr.voidreasonid

LEFT JOIN [dbo].[Receipt] AS r WITH (NOLOCK)
ON ri.receiptid = r.receiptid

JOIN (SELECT DISTINCT -- Distinct is used to stop duplicate receiptid's been displayed in the report, otherwise
ReceiptId -- receiptid would be duplicated for each receiptitem appearing on the order
,CarrierMethodId -- just want to obtain the despatch info once not multiple times
,DespatchDate
FROM dbo.DespatchConfirmation WITH (NOLOCK)) AS dc
ON r.ReceiptId = dc.ReceiptId

INNER JOIN dbo.PosItem pos WITH (NOLOCK)
ON vi.voidItemId = pos.voidItemId

LEFT JOIN dbo.Inventory AS i WITH (NOLOCK)
ON ri.InventoryId = i.InventoryId

--WHERE ri.receiptid = 11557396

--ORDER BY enteredbyreasonid, CONVERT(VARCHAR(8),vi.DateEntered,112)
ORDER BY r.ReceiptId

SET NOCOUNT OFF
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-05-15 : 07:30:34

Is there any difference in the execution plans between the 2 approaches?

Badjjl
Starting Member

6 Posts

Posted - 2009-05-15 : 07:46:45

Hi vijayisonly
Thanks for replying, yes does seem to be a difference in the execution plan when running as a select statement and as a store procedure. The main difference is the select execution does a index scan on positem table costing 12%, but the same execution on the stored procedure costs 66% on the Positem table. Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-15 : 13:33:36

seems like a case of parameter sniffing

http://omnibuzz-sql.blogspot.com/2006/11/parameter-sniffing-stored-procedures.html

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources