deadlock victim even using temp table

Hi. I am struggling to understand why I get the following error in
using the stored procedure noted below. I recently starting using a
temp table as a way of providing custom paging in asp.net and this
problem has occured ever since (maybe 10 times per day with an average
of 30 users on all day).
Here is the error: "Transaction (Process ID ##) was deadlocked on lock
resources with another process and has been chosen as the deadlock
victim. Rerun the transaction"
The client code is below. It uses a DataAdapter to fill a dataset that
is used to populate a datagrid. The long store procedure is below
that. It essentialy fills the temp table with records that are chosen
then retrieves all the necessary fields for the datagrid using whatever
page that is selected. There is code in there to support sorting and
hopefully it's not too confusing.
I apologize for the long post, I didn't want to remove parts of the SP
to make it shorter in case I removed an important element. I admit, I
am only an intermediate programmer so I may be missing some
fundamentals. Hopefully this is obvious to someone.
Thanks in advance.
Jeff
-- client code --
' Create Instance of Connection and Command Object
Dim myConnection As New
SqlConnection(ConfigurationSettings.AppSettings("connectionString"))
Dim myCommand As New SqlDataAdapter("tochange",
myConnection)
'Dim myCommand As New SqlDataAdapter
'myCommand.SelectCommand.Connection = myConnection
myCommand.SelectCommand.CommandType =
CommandType.StoredProcedure
myCommand.SelectCommand.CommandText =
"dbo.csp_cGeneral_GetRequests2"
myCommand.SelectCommand.Parameters.Add("@.PortalID",
SqlDbType.Int).Value = iPortalID
myCommand.SelectCommand.Parameters.Add("@.Status",
SqlDbType.VarChar, 10).Value = Status
myCommand.SelectCommand.Parameters.Add("@.RequestSeqID",
SqlDbType.Int).Value = RequestSeqID
myCommand.SelectCommand.Parameters.Add("@.BorrowerLastName",
SqlDbType.VarChar, 30).Value = BorrowerLastName
myCommand.SelectCommand.Parameters.Add("@.LoanOfficerCompany",
SqlDbType.VarChar, 30).Value = LoanOfficerCompany
myCommand.SelectCommand.Parameters.Add("@.BegDate",
SqlDbType.VarChar, 25).Value = BegDate
myCommand.SelectCommand.Parameters.Add("@.EndDate",
SqlDbType.VarChar, 25).Value = EndDate
myCommand.SelectCommand.Parameters.Add("@.ContactName",
SqlDbType.VarChar, 30).Value = ContactName
myCommand.SelectCommand.Parameters.Add("@.AgentID",
SqlDbType.Int).Value = AgentID
myCommand.SelectCommand.Parameters.Add("@.HasDocs",
SqlDbType.Int).Value = HasDocs
myCommand.SelectCommand.Parameters.Add("@.AssignedStaffID",
SqlDbType.Int).Value = iStaffSearchID
myCommand.SelectCommand.Parameters.Add("@.CurrentPage",
SqlDbType.Int).Value = _currentPageNumber
myCommand.SelectCommand.Parameters.Add("@.PageSize",
SqlDbType.Int).Value = iPagesize
myCommand.SelectCommand.Parameters.Add("@.SortField",
SqlDbType.VarChar, 30).Value = strSortColumn
myCommand.SelectCommand.Parameters.Add("@.UserID",
SqlDbType.Int).Value = UserID
myCommand.SelectCommand.Parameters.Add("@.Role",
SqlDbType.VarChar, 20).Value = Role
myCommand.SelectCommand.Parameters.Add("@.Maps",
SqlDbType.VarChar, 20).Value = sMaps
' Create and Fill the DataSet
Dim myDataSet As New DataSet
myCommand.Fill(myDataSet, "Requests")
Dim myTable As DataTable = myDataSet.Tables("Requests")
If Not myTable Is Nothing Then
If myTable.Rows.Count > 0 Then
Dim dr As DataRow = myTable.Rows(0)
_TotalRecords = dr.Item("TotalRecords")
Else
_TotalRecords = 0
End If
End If
' Return the DataSet
-- stored procedure --
ALTER procedure dbo.csp_cGeneral_GetRequests2
@.PortalID int,
@.Status varchar(10) = "-1",
@.RequestSeqID int = -1,
@.BorrowerLastName varchar(30) = "-1",
@.LoanOfficerCompany varchar(30) = "-1",
@.BegDate varchar(25) = "-1",
@.EndDate varchar(25) = "-1",
@.ContactName varchar(30) = "-1",
@.AgentID Int = Null,
@.UserID int = 0,
@.Role varchar(20) = 'None',
@.HasDocs decimal = -1,
@.CurrentPage int,
@.PageSize int,
@.SortField varchar(30),
@.Maps varchar(20),
@.AssignedStaffID Int --(-1 all, -2 not in list)
as
--if @.AssignedStaffID = -2
--begin
--
--end
Declare @.TotalRecords int
Declare @.Status1 int
Declare @.Status2 int
Declare @.AssignedAgentID int
Declare @.CustomerID int
set @.AssignedAgentID = 0
set @.CustomerID = 0
if @.Role = 'NotaryAgent'
Begin
set @.AssignedAgentID = @.UserID
set @.CustomerID = Null
end
if @.Role = 'Customer'
Begin
set @.AssignedAgentID = Null
set @.CustomerID = @.UserID
end
if @.Role = 'ServiceOwner'
Begin
set @.AssignedAgentID = Null
set @.CustomerID = Null
end
if @.Role = 'AgentOwner'
Begin
set @.AssignedAgentID = Null
set @.CustomerID = Null
end
set @.Status1 = -1
set @.Status2 = -1
if len(@.Status) = 1 or (len(@.Status) = 2 and not @.Status = '45')
begin
set @.Status1 = cast(@.Status as int)
set @.Status2 = cast(@.Status as int)
end
else
begin
if @.Status = '123'
Begin
set @.Status1 = 1
set @.Status2 = 3
end
if @.Status = '45'
Begin
set @.Status1 = 4
set @.Status2 = 5
end
if @.Status = '123456'
Begin
set @.Status1 = 1
set @.Status2 = 6
end
if @.Status = '1234569'
Begin
set @.Status1 = 1
set @.Status2 = 10
end
end
Declare @.BegDate2 as smalldatetime
Declare @.EndDate2 as smalldatetime
if @.BegDate = '-1' or @.EndDate = '-1' or isdate(@.BegDate) = 0 or
isdate(@.EndDate) = 0
begin
set @.BegDate2 = Null
Set @.EndDate2 = Null
end
else
begin
set @.BegDate2 = cast(@.BegDate as smalldatetime)
Set @.EndDate2 = cast(@.EndDate as smalldatetime)
end
CREATE TABLE #TempTable
(
ID int IDENTITY PRIMARY KEY,
RequestID int,
FileSize int,
DocCount int
)
INSERT INTO #TempTable
(
RequestID,
FileSize,
DocCount
)
SELECT
RequestID,
FileSize,
DocCount
>From (
Select SR.RequestID, isnull(FileSize,0) as FileSize,
isnull(temp1.DocCount,0) as DocCount,
CASE @.SortField
WHEN 'Request' THEN 0
WHEN 'Status' THEN SRS.StatusOrder
WHEN 'Borrower' THEN 0
WHEN 'Date' THEN 0
WHEN 'Location' THEN 0
WHEN 'ContactInfo' THEN 0
WHEN 'Agent' THEN 0
WHEN 'FileSize' THEN 0
WHEN 'HasCust' THEN SR.UserID
WHEN 'StaffOrder' THEN Staff.StaffOrder
ELSE SRS.StatusOrder
END AS sortcol0,
CASE @.SortField
WHEN 'Request' THEN ''
WHEN 'Status' THEN ''
WHEN 'Borrower' THEN SR.BorrowerLastName
WHEN 'Date' THEN convert(varchar(20),SR.SigningDate,112)
WHEN 'Location' THEN isnull(SR.SigningCity,'')
WHEN 'ContactInfo' THEN SR.ContactName
WHEN 'Agent' THEN Users.LastName
WHEN 'FileSize' THEN ''
WHEN 'HasCust' THEN '0'
WHEN 'StaffOrder' THEN Staff.StaffInitials
ELSE ''
END AS sortcol1,
CASE @.SortField
WHEN 'Request' THEN '0'
WHEN 'Status' THEN convert(varchar(20),SR.SigningDate,112)
WHEN 'Borrower' THEN SR.BorrowerFirstName
WHEN 'Date' THEN SR.SigningTime
WHEN 'Location' THEN isnull(SR.SigningState,'')
WHEN 'ContactInfo' THEN SR.LoanOfficerCompany
WHEN 'Agent' THEN Users.FirstName
WHEN 'FileSize' THEN '0'
WHEN 'HasCust' THEN '0'
WHEN 'StaffOrder' THEN '0'
ELSE convert(varchar(20),SR.SigningDate,112)
END AS sortcol2,
CASE @.SortField
WHEN 'Request' THEN '0'
WHEN 'Status' THEN SR.SigningTime
WHEN 'Borrower' THEN '0'
WHEN 'Date' THEN '0'
WHEN 'Location' THEN '0'
WHEN 'ContactInfo' THEN '0'
WHEN 'Agent' THEN '0'
WHEN 'FileSize' THEN '0'
WHEN 'HasCust' THEN '0'
WHEN 'StaffOrder' THEN '0'
ELSE SR.SigningTime
END AS sortcol3,
CASE @.SortField
WHEN 'Request' THEN 0
WHEN 'Status' THEN 0
WHEN 'Borrower' THEN 0
WHEN 'Date' THEN 0
WHEN 'Location' THEN 0
WHEN 'ContactInfo' THEN 0
WHEN 'Agent' THEN 0
WHEN 'FileSize' THEN FileSize
WHEN 'HasCust' THEN 0
WHEN 'StaffOrder' THEN 0
ELSE 0
END AS sortcol4,
SR.RequestSeqID AS sortcol5
>From dbo.ctbl_SigningRequests SR
Left Outer Join dbo.ctbl_SigningRequestStatus SRS
ON SR.SigningStatusID = SRS.SigningStatusID
Left Outer Join dbo.ctbl_UserData UD
ON SR.AssignedAgent = UD.UserID
Left Outer Join dbo.Users Users
ON SR.AssignedAgent = Users.UserID
Left Outer Join ctbl_PortalData
On ctbl_PortalData.PortalID = @.PortalID
Left Outer Join dbo.Users Staff
ON SR.AssignedStaffID = Staff.UserID
Left Outer Join (
Select ctbl_Docs.RequestID,
case when sum(Case when ctbl_Docs.LoanDocs = 0 and
ctbl_Docs.TitleDocs = 0 then 1 else 0 end) > 0 then 1 else 0 end as
DocCount,
cast(sum(ctbl_Docs.filesize) as decimal)/1000000 as filesize from
ctbl_Docs
Where ctbl_Docs.PortalID = @.PortalID
Group by ctbl_Docs.RequestID
) as temp1 ON SR.RequestID = temp1.RequestID
Where
SR.PortalID = @.PortalID
and SR.InActiveDate is null
and SR.BorrowerLastName like
IsNull(nullif('%'+@.BorrowerLastName+'%',
'%-1%'),'%'+SR.BorrowerLastName+'%')
and SR.LoanOfficerCompany like
IsNull(nullif('%'+@.LoanOfficerCompany+'%
','%-1%'),'%'+SR.LoanOfficerCompany+
'%')
and SR.ContactName like
IsNull(nullif('%'+@.ContactName+'%','%-1%'),'%'+SR.ContactName+'%')
and SR.RequestSeqID =
isnull(Nullif(@.RequestSeqID,-1),SR.RequestSeqID)
and IsNull(SR.AssignedAgent,-1) =
isnull(Nullif(@.AgentID,-1),IsNull(SR.AssignedAgent,-1))
and SR.SigningStatusID Between
IsNull(Nullif(@.Status1,-1),SR.SigningStatusID) and
IsNull(NullIf(@.Status2,-1),SR.SigningStatusID)
and SR.SigningDate Between IsNull(@.BegDate2,SR.SigningDate) and
IsNull(@.EndDate2,SR.SigningDate)
and SR.DeleteDate Is Null
and isnull(temp1.FileSize,0) > cast(@.HasDocs as decimal)
and SR.UserID = IsNull(@.CustomerID,SR.UserID)
and IsNull(SR.AssignedAgent,-1) =
isnull(Nullif(@.AssignedAgentID,-1),IsNull(SR.AssignedAgent,-1))
and IsNull(SR.AssignedStaffID,-1) =
isnull(Nullif(@.AssignedStaffID,-1),IsNull(SR.AssignedStaffID,-1))
) as t1
order by sortcol0, sortcol1, sortcol2, sortcol3, sortcol4 DESC,
sortcol5
--Create variable to identify the first and last record that should be
selected
SELECT @.TotalRecords = COUNT(*) FROM #TempTable
if @.CurrentPage > ceiling(cast(@.TotalRecords as float)/cast (@.PageSize
as float))
set @.CurrentPage = isnull(ceiling(@.TotalRecords / @.PageSize),1)
--select ceiling(cast(@.TotalRecords as float)/cast (@.PageSize as
float))
--select ceiling(cast(31/10 as float))
DECLARE @.FirstRec int, @.LastRec int
SELECT @.FirstRec = (@.CurrentPage - 1) * @.PageSize
SELECT @.LastRec = (@.CurrentPage * @.PageSize + 1)
--Return the total number of records available as an output parameter
--Select one page of data based on the record numbers above
Select SR.RequestID, SR.RequestSeqID, SRS.StatusNameShort, SR.UserID,
SRS.StatusOrder, SR.SigningDate, SR.SigningTime, SR.LoanNumber,
Case When SR.InvoiceCreated is Null then 0 else 1 end as
InvoiceCreated,
Case When SR.Invoiced is Null then 0 else 1 end as Invoiced,
Case When SR.InvoicePaid is Null then 0 else 1 end as CustomerPaid,
Case When SR.NotaryPaid is Null then 0 else 1 end as NotaryPaid,
Case When SR.Invoiced is Null then '0' else '1' end + Case When
SR.InvoicePaid is Null then '0' else '1' end
+ Case When SR.NotaryPaid is Null then '0' else '1' end as IconSort,
SR.ContactName, Isnull(SR.ContactEmail,'') as ContactEmail,
Isnull(SR.ContactPhone,'') as ContactPhone,
-- SR.ContactName + '' + left(SR.LoanOfficerCompany,10) as
ContactInfo, SR.BorrowerLastName, SR.BorrowerFirstName,
Left(SR.ContactName,15) as ContactInfo, SR.BorrowerLastName,
SR.BorrowerFirstName,
SR.LoanOfficerCompany, left(SR.LoanOfficerCompany,10) as
LoanOfficerCompanyShort,
Case When Users.UserID Is Null then '(Assign Notary)' else
Users.FirstName + ' ' + Users.LastName end as AssignedAgentName,
IsNull(Users.UserID,0) as AssignedAgentID, isnull(SR.SigningCity,'')
+ ', ' + isnull(SR.SigningState,'') as CityState,
isnull(SR.SigningZip,'') as SigningZip,
SR.LastChangedByMobile,
cast(isnull(SR.DocsIn,0) as char(1)) + cast(isnull(SR.HudIn,0) as
char(1)) + cast(#TempTable.DocCount as char(1)) as HudDocsFlag,
Case cast(isnull(SR.DocsIn,0) as char(1)) + cast(isnull(SR.HudIn,0) as
char(1)) + cast(#TempTable.DocCount as char(1))
When '001' then 'Loan Docs and Title are not in. Other docs exist.'
When '011' then 'Loan Docs are not in. Title is in. Other docs
exist.'
When '101' then 'Loan Docs are in. Title is not in. Other docs
exist.'
When '111' then 'Loan Docs and Title are in. Other docs exist.'
When '000' then 'Loan Docs and Title are not in.'
When '010' then 'Loan Docs are not in. Title is in.'
When '100' then 'Loan Docs are in. Title is not in.'
When '110' then 'Loan Docs and Title are in.'
else 'Desc error.'
end as HudDocsFlagDesc,
#TempTable.FileSize,
Case When SR.UserID = 0 then '-' else 'C' end as CustomerFlag,
Case When SR.AssignedAgent = 0 and @.Maps='MSN' then
''
When SR.AssignedAgent <> 0 and @.Maps='MSN' then
replace(replace('http://maps.msn.com/directionsFind.aspx?strt1=' +
isnull(Users.Street,'') + '&city1=' + isnull(Users.City,'') + '&zipc1='
+ isnull(Users.PostalCode,'')
+ '&cnty1=0&strt2=' + isnull(SR.SigningAddress1,'') + '&city2=' +
isnull(SR.SigningCity,'') + '&zipc2=' + isnull(SR.SigningZip,'') +
'&cnty2=0&rtyp=1&unit=0',' ','%20'),'#','')
When SR.AssignedAgent = 0 and @.Maps='QUEST' then
''
When SR.AssignedAgent <> 0 and @.Maps='QUEST' then
replace(replace('http://www.mapquest.com/directions/main.adp?go=1&do=nw&rmm=
1&un=m&cl=EN&ct=NA&rsres=1&1a='
+ isnull(Users.Street,'') + '&1c=' + isnull(Users.City,'') + '&1s=' +
isnull(Users.Region,'') + '&1z=' + isnull(Users.PostalCode,'')
+ '&2a=' + isnull(SR.SigningAddress1,'') + '&2c=' +
isnull(SR.SigningCity,'') + '&2s=' + isnull(SR.SigningState,'') +
'&2z=' + isnull(SR.SigningZip,''),' ','%20'),'#','')
else ''
end as MapLink,
cast(isnull(ctbl_PortalData.FileSSLActivate,1) as varchar(1)) as
FileSSLActivate,
(isnull(SR.PriceQty_Agent1,0) * isnull(SR.PriceAmt_Agent1,0))
+(isnull(SR.PriceQty_Agent2,0) * isnull(SR.PriceAmt_Agent2,0))
+(isnull(SR.PriceQty_Agent3,0) * isnull(SR.PriceAmt_Agent3,0))
+(isnull(SR.PriceQty_Agent4,0) * isnull(SR.PriceAmt_Agent4,0))
+(isnull(SR.PriceQty_Agent5,0) * isnull(SR.PriceAmt_Agent5,0)) as
NotaryInvoiceTotal, @.TotalRecords as TotalRecords,
isnull(Staff.StaffInitials, Isnull(Staff.FirstName,'***')) as Staff,
isnull(Staff.StaffOrder,0) as StaffOrder
>From dbo.ctbl_SigningRequests SR
inner join #TempTable
on #TempTable.RequestID = SR.RequestID
Left Outer Join dbo.ctbl_SigningRequestStatus SRS
ON SR.SigningStatusID = SRS.SigningStatusID
Left Outer Join dbo.ctbl_UserData UD
ON SR.AssignedAgent = UD.UserID
Left Outer Join dbo.Users Users
ON SR.AssignedAgent = Users.UserID
Left Outer Join dbo.Users Staff
ON SR.AssignedStaffID = Staff.UserID
Left Outer Join ctbl_PortalData
On ctbl_PortalData.PortalID = @.PortalID
WHERE
ID > @.FirstRec
AND
ID < @.LastRec
order by #TempTable.[ID]Check the transaction isolation level: you can probably live with READ
COMMITTED. Also make sure that the procedure isn't running in the context o
f
a transaction. If that isn't the problem, then make sure that indexes exist
on the columns joined and that the execution plan uses them. You may have t
o
coerce the optimizer with a hint or two.
"jhonz@.etsmail.com" wrote:

> Hi. I am struggling to understand why I get the following error in
> using the stored procedure noted below. I recently starting using a
> temp table as a way of providing custom paging in asp.net and this
> problem has occured ever since (maybe 10 times per day with an average
> of 30 users on all day).
> Here is the error: "Transaction (Process ID ##) was deadlocked on lock
> resources with another process and has been chosen as the deadlock
> victim. Rerun the transaction"
> The client code is below. It uses a DataAdapter to fill a dataset that
> is used to populate a datagrid. The long store procedure is below
> that. It essentialy fills the temp table with records that are chosen
> then retrieves all the necessary fields for the datagrid using whatever
> page that is selected. There is code in there to support sorting and
> hopefully it's not too confusing.
> I apologize for the long post, I didn't want to remove parts of the SP
> to make it shorter in case I removed an important element. I admit, I
> am only an intermediate programmer so I may be missing some
> fundamentals. Hopefully this is obvious to someone.
> Thanks in advance.
> Jeff
>
> -- client code --
> ' Create Instance of Connection and Command Object
> Dim myConnection As New
> SqlConnection(ConfigurationSettings.AppSettings("connectionString"))
> Dim myCommand As New SqlDataAdapter("tochange",
> myConnection)
> 'Dim myCommand As New SqlDataAdapter
> 'myCommand.SelectCommand.Connection = myConnection
> myCommand.SelectCommand.CommandType =
> CommandType.StoredProcedure
> myCommand.SelectCommand.CommandText =
> "dbo.csp_cGeneral_GetRequests2"
> myCommand.SelectCommand.Parameters.Add("@.PortalID",
> SqlDbType.Int).Value = iPortalID
> myCommand.SelectCommand.Parameters.Add("@.Status",
> SqlDbType.VarChar, 10).Value = Status
> myCommand.SelectCommand.Parameters.Add("@.RequestSeqID",
> SqlDbType.Int).Value = RequestSeqID
> myCommand.SelectCommand.Parameters.Add("@.BorrowerLastName",
> SqlDbType.VarChar, 30).Value = BorrowerLastName
> myCommand.SelectCommand.Parameters.Add("@.LoanOfficerCompany",
> SqlDbType.VarChar, 30).Value = LoanOfficerCompany
> myCommand.SelectCommand.Parameters.Add("@.BegDate",
> SqlDbType.VarChar, 25).Value = BegDate
> myCommand.SelectCommand.Parameters.Add("@.EndDate",
> SqlDbType.VarChar, 25).Value = EndDate
> myCommand.SelectCommand.Parameters.Add("@.ContactName",
> SqlDbType.VarChar, 30).Value = ContactName
> myCommand.SelectCommand.Parameters.Add("@.AgentID",
> SqlDbType.Int).Value = AgentID
> myCommand.SelectCommand.Parameters.Add("@.HasDocs",
> SqlDbType.Int).Value = HasDocs
> myCommand.SelectCommand.Parameters.Add("@.AssignedStaffID",
> SqlDbType.Int).Value = iStaffSearchID
> myCommand.SelectCommand.Parameters.Add("@.CurrentPage",
> SqlDbType.Int).Value = _currentPageNumber
> myCommand.SelectCommand.Parameters.Add("@.PageSize",
> SqlDbType.Int).Value = iPagesize
> myCommand.SelectCommand.Parameters.Add("@.SortField",
> SqlDbType.VarChar, 30).Value = strSortColumn
> myCommand.SelectCommand.Parameters.Add("@.UserID",
> SqlDbType.Int).Value = UserID
> myCommand.SelectCommand.Parameters.Add("@.Role",
> SqlDbType.VarChar, 20).Value = Role
> myCommand.SelectCommand.Parameters.Add("@.Maps",
> SqlDbType.VarChar, 20).Value = sMaps
> ' Create and Fill the DataSet
> Dim myDataSet As New DataSet
> myCommand.Fill(myDataSet, "Requests")
> Dim myTable As DataTable = myDataSet.Tables("Requests")
> If Not myTable Is Nothing Then
> If myTable.Rows.Count > 0 Then
> Dim dr As DataRow = myTable.Rows(0)
> _TotalRecords = dr.Item("TotalRecords")
> Else
> _TotalRecords = 0
> End If
> End If
> ' Return the DataSet
>
> -- stored procedure --
> ALTER procedure dbo.csp_cGeneral_GetRequests2
> @.PortalID int,
> @.Status varchar(10) = "-1",
> @.RequestSeqID int = -1,
> @.BorrowerLastName varchar(30) = "-1",
> @.LoanOfficerCompany varchar(30) = "-1",
> @.BegDate varchar(25) = "-1",
> @.EndDate varchar(25) = "-1",
> @.ContactName varchar(30) = "-1",
> @.AgentID Int = Null,
> @.UserID int = 0,
> @.Role varchar(20) = 'None',
> @.HasDocs decimal = -1,
> @.CurrentPage int,
> @.PageSize int,
> @.SortField varchar(30),
> @.Maps varchar(20),
> @.AssignedStaffID Int --(-1 all, -2 not in list)
> as
> --if @.AssignedStaffID = -2
> --begin
> --
> --end
> Declare @.TotalRecords int
> Declare @.Status1 int
> Declare @.Status2 int
> Declare @.AssignedAgentID int
> Declare @.CustomerID int
> set @.AssignedAgentID = 0
> set @.CustomerID = 0
> if @.Role = 'NotaryAgent'
> Begin
> set @.AssignedAgentID = @.UserID
> set @.CustomerID = Null
> end
> if @.Role = 'Customer'
> Begin
> set @.AssignedAgentID = Null
> set @.CustomerID = @.UserID
> end
> if @.Role = 'ServiceOwner'
> Begin
> set @.AssignedAgentID = Null
> set @.CustomerID = Null
> end
> if @.Role = 'AgentOwner'
> Begin
> set @.AssignedAgentID = Null
> set @.CustomerID = Null
> end
> set @.Status1 = -1
> set @.Status2 = -1
> if len(@.Status) = 1 or (len(@.Status) = 2 and not @.Status = '45')
> begin
> set @.Status1 = cast(@.Status as int)
> set @.Status2 = cast(@.Status as int)
> end
> else
> begin
> if @.Status = '123'
> Begin
> set @.Status1 = 1
> set @.Status2 = 3
> end
> if @.Status = '45'
> Begin
> set @.Status1 = 4
> set @.Status2 = 5
> end
> if @.Status = '123456'
> Begin
> set @.Status1 = 1
> set @.Status2 = 6
> end
> if @.Status = '1234569'
> Begin
> set @.Status1 = 1
> set @.Status2 = 10
> end
> end
> Declare @.BegDate2 as smalldatetime
> Declare @.EndDate2 as smalldatetime
> if @.BegDate = '-1' or @.EndDate = '-1' or isdate(@.BegDate) = 0 or
> isdate(@.EndDate) = 0
> begin
> set @.BegDate2 = Null
> Set @.EndDate2 = Null
> end
> else
> begin
> set @.BegDate2 = cast(@.BegDate as smalldatetime)
> Set @.EndDate2 = cast(@.EndDate as smalldatetime)
> end
> CREATE TABLE #TempTable
> (
> ID int IDENTITY PRIMARY KEY,
> RequestID int,
> FileSize int,
> DocCount int
> )
> INSERT INTO #TempTable
> (
> RequestID,
> FileSize,
> DocCount
> )
> SELECT
> RequestID,
> FileSize,
> DocCount
> Select SR.RequestID, isnull(FileSize,0) as FileSize,
> isnull(temp1.DocCount,0) as DocCount,
> CASE @.SortField
> WHEN 'Request' THEN 0
> WHEN 'Status' THEN SRS.StatusOrder
> WHEN 'Borrower' THEN 0
> WHEN 'Date' THEN 0
> WHEN 'Location' THEN 0
> WHEN 'ContactInfo' THEN 0
> WHEN 'Agent' THEN 0
> WHEN 'FileSize' THEN 0
> WHEN 'HasCust' THEN SR.UserID
> WHEN 'StaffOrder' THEN Staff.StaffOrder
> ELSE SRS.StatusOrder
> END AS sortcol0,
> CASE @.SortField
> WHEN 'Request' THEN ''
> WHEN 'Status' THEN ''
> WHEN 'Borrower' THEN SR.BorrowerLastName
> WHEN 'Date' THEN convert(varchar(20),SR.SigningDate,112)
> WHEN 'Location' THEN isnull(SR.SigningCity,'')
> WHEN 'ContactInfo' THEN SR.ContactName
> WHEN 'Agent' THEN Users.LastName
> WHEN 'FileSize' THEN ''
> WHEN 'HasCust' THEN '0'
> WHEN 'StaffOrder' THEN Staff.StaffInitials
> ELSE ''
> END AS sortcol1,
> CASE @.SortField
> WHEN 'Request' THEN '0'
> WHEN 'Status' THEN convert(varchar(20),SR.SigningDate,112)
> WHEN 'Borrower' THEN SR.BorrowerFirstName
> WHEN 'Date' THEN SR.SigningTime
> WHEN 'Location' THEN isnull(SR.SigningState,'')
> WHEN 'ContactInfo' THEN SR.LoanOfficerCompany
> WHEN 'Agent' THEN Users.FirstName
> WHEN 'FileSize' THEN '0'
> WHEN 'HasCust' THEN '0'
> WHEN 'StaffOrder' THEN '0'
> ELSE convert(varchar(20),SR.SigningDate,112)
> END AS sortcol2,
> CASE @.SortField
> WHEN 'Request' THEN '0'
> WHEN 'Status' THEN SR.SigningTime
> WHEN 'Borrower' THEN '0'
> WHEN 'Date' THEN '0'
> WHEN 'Location' THEN '0'
> WHEN 'ContactInfo' THEN '0'
> WHEN 'Agent' THEN '0'
> WHEN 'FileSize' THEN '0'
> WHEN 'HasCust' THEN '0'
> WHEN 'StaffOrder' THEN '0'
> ELSE SR.SigningTime
> END AS sortcol3,
> CASE @.SortField
> WHEN 'Request' THEN 0
> WHEN 'Status' THEN 0
> WHEN 'Borrower' THEN 0
> WHEN 'Date' THEN 0
> WHEN 'Location' THEN 0
> WHEN 'ContactInfo' THEN 0
> WHEN 'Agent' THEN 0
> WHEN 'FileSize' THEN FileSize
> WHEN 'HasCust' THEN 0
> WHEN 'StaffOrder' THEN 0
> ELSE 0
> END AS sortcol4,
> SR.RequestSeqID AS sortcol5
> Left Outer Join dbo.ctbl_SigningRequestStatus SRS
> ON SR.SigningStatusID = SRS.SigningStatusID
> Left Outer Join dbo.ctbl_UserData UD
> ON SR.AssignedAgent = UD.UserID
> Left Outer Join dbo.Users Users
> ON SR.AssignedAgent = Users.UserID
> Left Outer Join ctbl_PortalData
> On ctbl_PortalData.PortalID = @.PortalID
> Left Outer Join dbo.Users Staff
> ON SR.AssignedStaffID = Staff.UserID
> Left Outer Join (
> Select ctbl_Docs.RequestID,
> case when sum(Case when ctbl_Docs.LoanDocs = 0 and
> ctbl_Docs.TitleDocs = 0 then 1 else 0 end) > 0 then 1 else 0 end as|||Thanks, Brian. Couldn't I use WITH (NOLOCK) on the select that fills
the temptable and then later selects from it? READ COMMITTED seems to
be SQL 2000 default and is probably arleady set. I image the locks or
on the select a temp table should not be shared amongts users. In
ASP.Net's connection pooling, do you think things could get crossed
there.
I am not running a transaction so I think I am safe there.
I ran the execution plan and I don't see any table scans. Is that
sufficient indication that things are OK there?
Jeff