Declare @SearchText nvarchar(max)
set @SearchText = ‘sah.CheckInCheckOutDateTime>=”2011-09-28 10:00:00.000”’

Declare @SchoolYearId uniqueidentifier
set @SchoolYearId=’25511F7F-8F87-4085-A242-2004F4419D36′

Declare @Str as nvarchar(max)
set @Str=’
select s.LastName +”, ” + s.FirstName as ”Name”,CheckInCheckOut,sah.CheckInCheckOutDateTime as ”ClockOut”
from StaffAttendenceHistory sah
join Staffschoolyear ssy on sah.staffschoolyearid = ssy.id
join Staff s on ssy.staffid = s.id
where ssy.SchoolYearId=@i’

If(LEN(ISNULL(@SearchText,0))>0)
set @Str = @Str + ‘ and ( ‘+ @SearchText + ‘)’

set @Str = @Str + ‘ order by CheckInCheckOutDateTime asc ‘

Declare @tbGI table (Id int identity(1,1), Name nvarchar(50), CheckInCheckOut bit, CheckInCheckOutDateTime datetime)
insert into @tbGI(Name,CheckInCheckOut,CheckInCheckOutDateTime)
exec sp_executesql @Str,N’@i uniqueidentifier’,@i=@SchoolYearId

select * from @tbGI

Advertisements