codice:
USE [BSPF_GLOB]
GO
/****** Object: StoredProcedure [dbo].[spLeaveRequestList2] Script Date: 02/26/2009 14:59:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spLeaveRequestList2]
@IdUser int,
@IdResponsible int,
@IdLeaveType int,
@LeaFromDate varchar(14),
@LeaToDate varchar(14),
@IdStatus int,
@IdApprovalManager int,
@query varchar(1000)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @DateStart datetime
DECLARE @DateEnd datetime
IF @IdUser = -1
SET @IdUser = 0
IF @IdResponsible = -1
SET @IdResponsible = 0
IF @IdLeaveType = -1
SET @IdLeaveType = 0
IF @LeaFromDate = ''
SET @LeaFromDate ='19000101000000'
IF @LeaToDate = ''
SET @LeaFromDate ='21001231000000'
IF @IdStatus = -1
SET @IdStatus = 0
IF @IdApprovalManager = -1
SET @IdApprovalManager = 0
SET @DateStart = dbo.StringToData(@LeaFromDate)
SET @DateEnd = dbo.StringToData(@LeaToDate)
IF @QUERY IS NULL
SELECT Ler.IDLeaveRequest,
Ler.IDUser,
Ler.IDLeaveType,
dbo.DataToString(Ler.leaFromDate) as leaFromDate,
dbo.DataToString(Ler.leaTODate) as leaToDate,
Ler.leaAmount,
Ler.leaAmountBaseUnit,
Ler.leaReason,
Ler.leaContactAddress,
Ler.leaContactPhone,
Ler.leaApproval,
isnull(Ler.IDApprovalManager, -1) as IDApprovalManager,
Ler.IDStatus,
Ler.leaManagerNote
FROM leaveRequest Ler
WHERE Ler.IDUser = ISNULL(@IdUser, Ler.IDUser)
AND Ler.IDLeaveType = ISNULL(@IdLeaveType, Ler.IDLeaveType)
AND Ler.leaFromDate >= @DateStart
AND Ler.leaTODate <= @DateEnd
AND Ler.IDStatus = ISNULL(@IdStatus, Ler.IDStatus)
AND ((Ler.IDApprovalManager = ISNULL(@IdApprovalManager, Ler.IDApprovalManager))
or (@IdApprovalManager is null and Ler.IDApprovalManager is null))
ELSE
exec (' SELECT Ler.IDLeaveRequest,
Ler.IDUser,
Ler.IDLeaveType,
dbo.DataToString(Ler.leaFromDate) as leaFromDate,
dbo.DataToString(Ler.leaTODate) as leaToDate,
Ler.leaAmount,
Ler.leaAmountBaseUnit,
Ler.leaReason,
Ler.leaContactAddress,
Ler.leaContactPhone,
Ler.leaApproval,
isnull(Ler.IDApprovalManager, -1) as IDApprovalManager,
Ler.IDStatus,
Ler.leaManagerNote
FROM leaveRequest Ler
WHERE Ler.IDUser = ISNULL(' + @IdUser +', Ler.IDUser)
AND Ler.IDLeaveType = ISNULL(' + @IdLeaveType + ', Ler.IDLeaveType)
AND Ler.leaFromDate >= ' + @DateStart +'
AND Ler.leaTODate <= '+ @DateEnd + '
AND Ler.IDStatus = ISNULL(' + @IdStatus +', Ler.IDStatus)
AND ((Ler.IDApprovalManager = ISNULL(' + @IdApprovalManager + ', Ler.IDApprovalManager))
or (' + @IdApprovalManager + ' is null and Ler.IDApprovalManager is null))
AND Ler.IDUser IN (' + @query + ')
')
END
Non lo postata prima perchè pensavo fosse troppo lunga da inserire in una discussione.