DECLARE @user_id nvarchar(50)
DECLARE @case_type nvarchar(50)
DECLARE @case_status nvarchar(50)
DECLARE @page_size int
DECLARE @page_current int
DECLARE @item_count int
DECLARE @sql nvarchar(4000)
DECLARE @sql_temp nvarchar(1000)
DECLARE @sql_count nvarchar(4000)
DECLARE @sql_para nvarchar(4000)
DECLARE @sql_result nvarchar(4000)
DECLARE @orderby nvarchar(200)
DECLARE @canceled_status nvarchar(200)
DECLARE @Now smalldatetime
SET @Now = GETDATE()
SET @page_size = 20
SET @page_current = 1
SET @user_id = 'CEDDBB98-3023-4C61-9699-A8ABE5E13ED7'
SET @case_type = 'PR'
SET @item_count = 0
SET @case_status = 'finish'
set @canceled_status='canceled'
SET @sql = N'SELECT DISTINCT C.case_id,P.create_date AS process_date, P.process_id' +
' FROM MemberShip.dbo.users U INNER JOIN processes P with (nolock) ON U.user_id=P.process_user_id INNER JOIN cases C with (nolock) ON P.case_id=C.case_id INNER JOIN QueryTypeConfig Q ON Q.case_type = C.case_type ' +
--' left join PO_Confirm PC on PC.case_ID = C.Case_ID ' +
' WHERE '+
--' PC.confirm_status = 0 and '+
' P.finish_date IS NOT NULL AND ' +
' (C.Track_Status IS NULL OR C.Track_Status <> ''CANCELED'' ) AND ' +
' C.is_reject=0 AND Q.query_type = @case_type AND' +
' P.process_no=(SELECT MAX(process_no) FROM processes with (nolock) WHERE case_id=C.case_id) AND' +
' (EXISTS(SELECT P2.process_id FROM processes P2 with (nolock) WHERE P2.case_id=C.case_id AND P2.finish_date IS NOT NULL AND P2.process_user_id=@user_id)' +
' OR EXISTS(SELECT P2.process_id FROM processes P2 with (nolock) INNER JOIN MemberShip.dbo.Attorney Att ON Att.user_id = P2.process_user_id WHERE P2.case_id=C.case_id AND P2.finish_date IS NOT NULL AND Att.user_id_attorney=@user_id' +
' AND Att.validate_date_from<=@Now AND Att.validate_date_to>=@Now AND Att.is_accept=1 ) ' +
' OR EXISTS(SELECT P2.process_id FROM processes P2 with (nolock) INNER JOIN Processes_SpecialSign PS ON P2.process_id = PS.process_id WHERE P2.case_id=C.case_id AND PS.finish_date IS NOT NULL AND PS.user_id=@user_id)' +
' OR EXISTS(SELECT P2.process_id FROM processes P2 with (nolock) INNER JOIN Processes_SpecialSign PS ON P2.process_id = PS.process_id INNER JOIN MemberShip.dbo.Attorney Att ON Att.user_id = PS.user_id WHERE P2.case_id=C.case_id AND' +
' PS.finish_date IS NOT NULL AND Att.user_id_attorney=@user_id AND Att.validate_date_from<=@Now AND Att.validate_date_to>=@Now AND Att.is_accept=1 ))' +
' AND P.create_date >= DATEADD( month, -1, @Now ) AND P.create_date <= @Now'
print @sql
EXEC sp_executesql @sql,N'@user_id nvarchar(50), @Now smalldatetime,@case_type nvarchar(50)',@user_id,@Now,@case_type
-------------------------------------------------------------------------------------------------------------------------------------
如果直接執行print處理的sql，大約3S，
但通過sp_executesql來執行，直接跑死。最近DB Server被這個拖死了。
哪位大大有例似經驗的，取個經。
