Hi All,
I am created a SP with name "Attachments" and i am calling this SP form my application. When this SP is Called i am getting below error
CustomRequest.executeProcedureRq procedureName="ATR_Attachments">Parameters>Parameter>N'ATR/R/283754.01'/Parameter>/Parameters>/CustomRequest.executeProcedureRq>: Error processing request [CustomRequest.executeProcedureRq].: Exception has been thrown by the target of an invocation.: [Microsoft][ODBC SQL Server Driver]Timeout expired.
i think there is a problem in my SP where i am using right outer join
please view below SP
USE [ExampleData]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[ATR_Attachments] @PolicyNumber VARCHAR(50)
AS
BEGIN
DECLARE @ModifiedPolicyNumber nvarchar(100),@Count int,@ModifiedPolicyNumber1 nvarchar(100),@ModifiedPolicyNumber2 nvarchar(100)
SELECT @count=(SELECT len(@PolicyNumber)-len(replace(@PolicyNumber,'/','')))
IF @count >2
BEGIN
DECLARE @test nvarchar(1000)
SELECT @test=(SELECT substring(@PolicyNumber,charindex('/',@PolicyNumber)+1,len(@PolicyNumber)))
SELECT @test=(SELECT substring(@test,charindex('/',@test)+1,len(@test)))
SELECT @test=(SELECT substring(@test,charindex('/',@test)+1,len(@test)))
SELECT @test=(SELECT substring(@PolicyNumber,0,len(@PolicyNumber)-len(@test)))
SELECT @ModifiedPolicyNumber=@test
END
ELSE
BEGIN
SELECT @ModifiedPolicyNumber=@PolicyNumber
END
IF @ModifiedPolicyNumber like '%.%'
BEGIN
SELECT @ModifiedPolicyNumber=(select substring(@ModifiedPolicyNumber,0,charindex('.',@ModifiedPolicyNumber)))
END
ELSE
BEGIN
SELECT @ModifiedPolicyNumber=(@ModifiedPolicyNumber)
END
select @ModifiedPolicyNumber1=(@ModifiedPolicyNumber+'.%')
select @ModifiedPolicyNumber2= (@ModifiedPolicyNumber+'/%')
INSERT INTO attachments
SELECT DISTINCT s1.Quoteid as Quoteid, s2.Moniker AS Moniker, s2.caption AS caption, s2.FileName AS FileName, getdate() AS AttachDate, s2.MessageID AS MessageID
FROM attachments s1 WITH(NOLOCK)
right outer join
(SELECT Quoteid, caption,FileName,Moniker,AttachDate,MessageID FROM attachments WITH(NOLOCK) WHERE Quoteid in (SELECT quoteid FROM Quote WITH(NOLOCK) WHERE LOB not like'CLAIM' and (PolicyNumber=@ModifiedPolicyNumber or PolicyNumber like @ModifiedPolicyNumber1 or PolicyNumber like @ModifiedPolicyNumber2))
And Caption not like 'PRINTATTACHMENT%') s2
ON s1.Quoteid <> s2.Quoteid and s1.moniker<>s2.moniker and s1.caption<>s2.caption WHERE
S1.Quoteid in (SELECT quoteid FROM quote WITH(NOLOCK) WHERE LOB not like'CLAIM%' and (PolicyNumber=@ModifiedPolicyNumber or PolicyNumber like @ModifiedPolicyNumber1 or PolicyNumber like @ModifiedPolicyNumber2))
AND
not exists (SELECT Quoteid, caption FROM attachments WITH(NOLOCK) WHERE Quoteid in (SELECT quoteid FROM quote WITH(NOLOCK) WHERE LOB not like'CLAIM%' and (PolicyNumber=@ModifiedPolicyNumber or PolicyNumber like @ModifiedPolicyNumber1 or PolicyNumber like @ModifiedPolicyNumber2)) and Quoteid = s1.Quoteid and moniker=s2.moniker and caption = s2.caption)and S1.quoteid > s2.quoteid
Select QuoteId from [ExampleData].[dbo].[Quote] WITH(NOLOCK) where PolicyNumber=@Policynumber;
RETURN @@ROWCOUNT;
END
I think we need add Indexs for the join to increase the performance. But i am new to indexs could any please help me on this.
I appreciate your efforts and time.
Thanks,