Help: Why Excute A Stored Procedure Need To More 30 Seconds, But Direct Excute The Query Of This Procedure In Microsoft SQL Server Management Studio Under 1 Second

Hello to all,

I have a stored procedure. If i give this command exce ShortestPath 3418, '4125', 5 in a script and excute it. It takes more 30 seconds time to be excuted.

but i excute it with the same parameters  direct in Microsoft SQL Server Management Studio , It takes only under 1 second time

I don't know why?

Maybe can somebody help me?

thanks in million

best Regards

Pinsha 

My Procedure Codes are here:set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author: <Author,,Name>

-- Create date: <Create Date,,>

-- Description: <Description,,>

-- =============================================

ALTER PROCEDURE [dbo].[ShortestPath] (@IDMember int, @IDOther varchar(1000),@Level int, @Path varchar(100) = null output )

AS

BEGIN

 

if ( @Level = 1)

begin

select @Path = convert(varchar(100),IDMember)

from wtcomValidRelationships

where wtcomValidRelationships.[IDMember]= @IDMember

and PATINDEX('%'+@IDOther+'%',(select RelationshipIDs from wtcomValidRelationships where IDMember = @IDMember) ) > 0

end

if (@Level = 2)

begin

select top 1 @Path = convert(varchar(100),A.IDMember)+'-'+convert(varchar(100),B.IDMember)

from wtcomValidRelationships as A, wtcomValidRelationships as B

where A.IDMember = @IDMember and charindex(convert(varchar(100),B.IDMember),A.RelationshipIDS) > 0

and PATINDEX('%'+@IDOther+'%',B.RelationshipIDs) > 0

end

if (@Level = 3)

begin

select top 1 @Path = convert(varchar(100),A.IDMember)+ '-'+convert(varchar(100),B.IDMember)+'-'+convert(varchar(100),C.IDMember)

from wtcomValidRelationships as A, wtcomValidRelationships as B, wtcomValidRelationships as C

where A.IDMember = @IDMember and charindex(convert(varchar(100),B.IDMember),A.RelationshipIDS) > 0

and charindex(convert(varchar(100),C.IDMember),B.RelationshipIDs) > 0 and PATINDEX('%'+@IDOther+'%',C.RelationshipIDs) > 0

end

if ( @Level = 4)

begin

select top 1 @Path = convert(varchar(100),A.IDMember)+ '-'+convert(varchar(100),B.IDMember)+'-'+convert(varchar(100),C.IDMember)+'-'+convert(varchar(100),D.IDMember)

from wtcomValidRelationships as A, wtcomValidRelationships as B, wtcomValidRelationships as C, wtcomValidRelationships as D

where A.IDMember = @IDMember and charindex(convert(varchar(100),B.IDMember),A.RelationshipIDS) > 0

and charindex(convert(varchar(100),C.IDMember),B.RelationshipIDs) > 0 and charindex(convert(varchar(100),D.IDMember), C.RelationshipIDs) > 0

and PATINDEX('%'+@IDOther+'%',D.RelationshipIDs) > 0

end

if (@Level = 5)

begin

select top 1 @Path = convert(varchar(100),A.IDMember)+ '-'+convert(varchar(100),B.IDMember)+'-'+convert(varchar(100),C.IDMember)+'-'+convert(varchar(100),D.IDMember)+'-'+convert(varchar(100),E.IDMember)

from wtcomValidRelationships as A, wtcomValidRelationships as B, wtcomValidRelationships as C, wtcomValidRelationships as D, wtcomValidRelationships as E

where A.IDMember = @IDMember and charindex(convert(varchar(100),B.IDMember),A.RelationshipIDS) > 0

and charindex(convert(varchar(100),C.IDMember),B.RelationshipIDs) > 0 and charindex(convert(varchar(100),D.IDMember), C.RelationshipIDs) > 0

and charindex(convert(varchar(100),E.IDMember),D.RelationshipIDs) > 0 and PATINDEX('%'+@IDOther+'%',E.RelationshipIDs) > 0

end

if (@Level = 6)

begin

select top 1 @Path = '' from wtcomValidRelationships

end

END