la store procedure è questa:
- Codice: Seleziona tutto
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 added to prevent extra result sets from
-- interfering with SELECT statements.
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 = '19000202000000'
IF @LeaToDate = ''
SET @LeaToDate = '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))
-- Insert statements for procedure here
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 riesco a capire dove sta l'errore.
Spero mi possiate aiutare!
Grazie