dynamic sql

USE [RTOManager_Demo]
GO
/****** Object:  StoredProcedure [dbo].[Proc_GEL_StudentFetchForGEL]    Script Date: 5/24/2022 9:43:05 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

----Procedure to fetch student information for GEL integration
----Created By: Yuvaraj Upadhyaya
----Created date: May 19 2022
ALTER PROCEDURE [dbo].[Proc_GEL_StudentFetchForGEL]  '2020-01-01'
(@LastSyncDatetime nvarchar(MAX)=NULL)
AS
BEGIN

DECLARE  @dynamicQuery nvarchar(MAX);
DECLARE  @params nvarchar(MAX);
DECLARE @convertedLastSyncDatetime nvarchar(MAX)='';

SET @dynamicQuery=' select s.StudentId,s.FirstName,s.LastName,s.Email,sc.StartDate,sc.FinishDate,(select top 1 status from StudentCourse_Log where studentId=s.StudentId order by StartDate desc) as Status from student s
    inner join (
    select scl.StudentId,max(scl.ChangedTime) as ChangedTime,min(scl.StartDate) as StartDate,max(scl.FinishDate) as FinishDate from studentcourse_log scl
    inner join course c on c.CourseID= scl.CourseId
    where c.CourseType=''ELICOS''
    group by scl.StudentId) as sc on sc.StudentId=s.StudentId '



    IF @LastSyncDatetime IS NOT NULL
    BEGIN

     SET @dynamicQuery= @dynamicQuery +  ' where sc.ChangedTime > @LastSyncDatetime'
     --'''' + convert(varchar, @LastSyncDatetime, 20) + '''';

    END
    print @dynamicQuery
    --Exec(@dynamicQuery);
    SET @params= '@LastSyncDatetime nvarchar(max)'

    EXEC sp_executesql @dynamicQuery,@params,@LastSyncDatetime=@LastSyncDatetime

END