SQL Server Performance Forum – Threads Archive

sp gives sql server timeout

we have lare volume transaction processing system and some time below SP gives timeout. Can any one help me what is wrong with this sp. Thanks Here is SP CREATE PROCEDURE dbo.wsp_CheckDuplicate
(
@MerchantID int,
@AccountNo varchar(17),
@AuthAmount money,
@CustID varchar(15),
@RefID varchar(50),
@TransCode varchar(2),
@TransID bigint output
)
AS
IF @MerchantID in (99999901, 99999903)
BEGIN
SET @TransID = -1
return
END
DECLARE @EncryptAccountno varchar(50)
SET @EncryptAccountno = dbo.fn_encrypt(@AccountNo) DECLARE @PostedDate varchar(30)
SET @PostedDate = CONVERT(varchar, GetDate(), 101)
IF @CustID <> ”
BEGIN SET @TransID = isNull((
SELECT Top 1 TransID from PaymentDetail WITH (NOLOCK, INDEX(idx_AccountNo))
where MerchantID = @MerchantID
and posteddate >= @PostedDate
and EncryptAccountNo = @EncryptAccountno
and Amount = @AuthAmount
and CustID = @CustID
and RefID = @RefID
and Status in (‘Q’, ‘P’, ‘C’)
AND TransCode = @TransCode
),-1) END
ELSE
BEGIN SET @TransID = isNull((
SELECT Top 1 TransID from PaymentDetail WITH (NOLOCK, INDEX(idx_AccountNo))
where MerchantID = @MerchantID
and posteddate >= @PostedDate
and EncryptAccountNo = @EncryptAccountno
and Amount = @AuthAmount
and RefID = @RefID
and Status in (‘Q’, ‘P’, ‘C’)
AND TransCode = @TransCode
),-1)
END
GO

Kindly don’t duplicates posts.
Luis Martin
Moderator
SQL-Server-Performance.com All in Love is Fair
Stevie Wonder
All postings are provided â€œAS ISâ€ with no warranties for accuracy.

]]>