Hi ,
can some one please help me on this.

My sql procedure to compare 2 comma seperated rows from consecutive rows is running fine when I execute from database.After passing parameters from front end it is throwing time out error.Not sure why this is happening...
here is my procedure:



alter Proc [dbo].[GetLatestPhasesEditHistory]
@ProjectID int
-- [GetLatestPhasesEditHistory] 216
As Begin

Declare @new varchar(1000),@old varchar(1000),@return varchar(1000), @i int, @tblCnt int
,@UpdatedDate datetime,@UpdatedBy varchar(10),@Phase varchar(1000)


If object_id('dbo.#t') is not null Begin ; drop table dbo.#t ;End
CREATE TABLE dbo.#t(Phase varchar(1000),UpdatedDate datetime,UpdatedBy varchar(10));
--CREATE INDEX IDX_phase ON #t(Id)

if (select COUNT(*) from phase WHERE PROJECT_ID=@ProjectID ) > 0
BEGIN
Set @tblCnt = (select COUNT(*) from phase WHERE PROJECT_ID=@ProjectID )
Set @i=1
While @i != (@tblCnt) Begin
set @new= (Select top 1 phase from (select Top (@i) phase,ID from Phase where project_id=@ProjectID order by ID desc) a order by ID asc)
set @old=(Select top 1 phase from (select Top (@i + 1) phase,ID from Phase where project_id=@ProjectID order by ID desc) a order by ID asc)
set @UpdatedDate=(select top 1 UpdatedDate from (select Top (@i)UpdatedDate,ID from phase where project_id=@ProjectID order by ID desc)a order by ID asc)
set @UpdatedBy= (select top 1 UpdatedBy from (Select top(@i) UpdatedBy,ID from phase where project_id=@ProjectID order by ID desc)a order by ID asc)
INSERT INTO #t (Phase)
exec [dbo].[PMT_GetPhasesEditHistory] @strN = @new,@strO = @old
--Update #t set UpdatedBy = @UpdatedBy, UpdatedDate=@UpdatedDate where Id = (select max(ID) from #t)
Set @i = @i +1
End
set @old = (Select top 1 phase from (select Top (@tblCnt) phase,ID from Phase where project_id=@ProjectID order by ID desc) a order by ID asc)
set @UpdatedDate=(Select top 1 UpdatedDate from (select Top (@tblCnt) UpdatedDate,ID from Phase where project_id=@ProjectID order by ID desc) a order by ID asc)
set @UpdatedBy= (Select top 1 UpdatedBy from (select Top (@tblCnt) UpdatedBy,ID from Phase where project_id=@ProjectID order by ID desc) a order by ID asc)
INSERT INTO #t (Phase,UpdatedDate,UpdatedBy) Values (@old,@UpdatedDate,@UpdatedBy)
END
select Phase,UpdatedDate,UpdatedBy from #t
drop table #t;
End

---------------------------------------------------------------------------------

My asp code for this is :

<%

Set objConnPhase=Server.CreateObject("ADODB.Connection ")
objConnPhase.CommandTimeout = 120
Set objRSPhase=Server.CreateObject("ADODB.Recordset")
Set ObjcmdPhase=Server.CreateObject("ADODB.Command")
objConnPhase.Open Application("conn")
Set ObjcmdPhase.ActiveConnection = objConnPhase
ObjcmdPhase.CommandText = "GetLatestPhasesEditHistory"
ObjcmdPhase.CommandType = adCmdStoredProc
ObjcmdPhase.Parameters.Append ObjcmdPhase.CreateParameter("@ProjectID", adInteger, adParamInput, 4, strProjectId)
set objRSPhase= ObjcmdPhase.Execute

%>
<%If Not (objRSPhase.BOF and objRSPhase.EOF) Then%>

<table id="tblphase" cellspacing="0" cellpadding="0" border="0" class="gridlines">
<tr class="grdHeading" align="left" style="font-weight:bold;white-space:nowrap;">
<%For intCol = 0 To 3%>
<th align="left">
<B><%=objRSPhase(intCol).Name%></B>
</th>
<% Next %>
</tr>
<%While Not objRSPhase.EOF %>
<tr class="grdRow">
<%For each item in objRSPhase.Fields %>
<td><%=cstr(objRSPhase(item.Name)) %></td>
<% Next %>
</tr>
<%objRSPhase.MoveNext %>
<%Wend%>
</table>
<%else %>
<div class="errormsg">No records could be found.</div>
<%End If%>