July 5, 2010 at 3:36 am
hi,
Iam having trouble executing this procedure.There are no errors during compilation.The
datatype of departuredate and returndate columns is datetime.when i send null values as parameters during execution of the stored procedure.It raises the above mentioned error.Please help me.
ALTER procedure [dbo].[Overseas_getEOIWaitingForApproval]
@DepartureDate datetime,
@ReturnDate datetime,
@Department varchar(100),
@YearGroup Varchar(20),
@ApprovalId int
as
declare @sql nvarchar(2000)
print isdate(cast(@DepartureDate as varchar(50)))
set @sql='Select OverseasExcursionId,TourTitle,Destination,DepartmentId,DepartmentName,
DepartureDate,ReturnDate From OverseasExcursionDetails where 1=1 '
+ case when @DepartureDate is null then '' else
'and (( cast(' + @DepartureDate + ' as datetime) >= DepartureDate and cast(' + @DepartureDate + ' as datetime)<= ReturnDate) or (cast('
+ @ReturnDate + ' as datetime) >= DepartureDate and cast(' + @ReturnDate + ' as datetime)<= ReturnDate) or (cast('
+ @DepartureDate + ' as datetime) <=DepartureDate and cast(' + @ReturnDate + ' as datetime)>= ReturnDate)) ' end
+ case when @Department is null then '' else ' and DepartmentName =''' + @Department + '''' end
+ case when @YearGroup is null then '' else ' and YearGroup = ''' + @YearGroup + '''' end
+ case when @ApprovalId is null then '' else ' and ApprovalId = ' + @ApprovalId end
July 5, 2010 at 4:41 am
This is how I would do it:
ALTER PROCEDURE [dbo].[Overseas_getEOIWaitingForApproval]
@DepartureDate DATETIME,
@ReturnDate DATETIME,
@Department VARCHAR(100),
@YearGroup VARCHAR(20),
@ApprovalId INT
AS
BEGIN
DECLARE @Sql NVARCHAR(2000)
SET @Sql='
SELECT OverseasExcursionId,TourTitle,Destination,DepartmentId,DepartmentName,
DepartureDate,ReturnDate
FROM OverseasExcursionDetails
WHERE 1=1 '
IF @DepartureDate IS NOT NULL
SET @Sql = @Sql +
'and (
(@DepartureDate >= DepartureDate and @DepartureDate <= ReturnDate)
or (@ReturnDate >= DepartureDate and @ReturnDate <= ReturnDate)
or (@DepartureDate <=DepartureDate and @ReturnDate >= ReturnDate)
) '
IF @Department IS NOT NULL
SET @Sql = @Sql +
' and DepartmentName = @Department '
IF @YearGroup IS NOT NULL
SET @Sql = @Sql +
' and YearGroup = @YearGroup '
IF @ApprovalId IS NOT NULL
SET @Sql = @Sql +
' and ApprovalId = @ApprovalId '
EXEC sp_executeSQL @sql,
N'@DepartureDate DATETIME,
@ReturnDate DATETIME,
@Department VARCHAR(100),
@YearGroup VARCHAR(20),
@ApprovalId INT',
@DepartureDate ,
@ReturnDate ,
@Department ,
@YearGroup ,
@ApprovalId
END
Haven't tried it. If you post table scripts I can verify it.
-- Gianluca Sartori
July 5, 2010 at 6:32 am
thank u very much, but i had to make some changes to make the query work.Once again thank u
July 5, 2010 at 6:37 am
Glad you found it helpful.
Next time, you could provide your table scripts and some sample data, so that one can test against your tables.
-- Gianluca Sartori
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply