Advertisement
yuvarajupadhyaya

dynamic sql

May 24th, 2022
2,035
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. USE [RTOManager_Demo]
  2. GO
  3. /****** Object:  StoredProcedure [dbo].[Proc_GEL_StudentFetchForGEL]    Script Date: 5/24/2022 9:43:05 AM ******/
  4. SET ANSI_NULLS ON
  5. GO
  6. SET QUOTED_IDENTIFIER ON
  7. GO
  8.  
  9. ----Procedure to fetch student information for GEL integration
  10. ----Created By: Yuvaraj Upadhyaya
  11. ----Created date: May 19 2022
  12. ALTER PROCEDURE [dbo].[Proc_GEL_StudentFetchForGEL]  '2020-01-01'
  13. (@LastSyncDatetime nvarchar(MAX)=NULL)
  14. AS
  15. BEGIN
  16.  
  17. DECLARE  @dynamicQuery nvarchar(MAX);
  18. DECLARE  @params nvarchar(MAX);
  19. DECLARE @convertedLastSyncDatetime nvarchar(MAX)='';
  20.  
  21. 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
  22.     inner join (
  23.     select scl.StudentId,max(scl.ChangedTime) as ChangedTime,min(scl.StartDate) as StartDate,max(scl.FinishDate) as FinishDate from studentcourse_log scl
  24.     inner join course c on c.CourseID= scl.CourseId
  25.     where c.CourseType=''ELICOS''
  26.     group by scl.StudentId) as sc on sc.StudentId=s.StudentId '
  27.  
  28.    
  29.  
  30.     IF @LastSyncDatetime IS NOT NULL
  31.     BEGIN
  32.      
  33.      SET @dynamicQuery= @dynamicQuery +  ' where sc.ChangedTime > @LastSyncDatetime'
  34.      --'''' + convert(varchar, @LastSyncDatetime, 20) + '''';
  35.    
  36.     END
  37.     print @dynamicQuery
  38.     --Exec(@dynamicQuery);
  39.     SET @params= '@LastSyncDatetime nvarchar(max)'
  40.    
  41.     EXEC sp_executesql @dynamicQuery,@params,@LastSyncDatetime=@LastSyncDatetime
  42.    
  43. END
  44.  
  45.  
Advertisement
RAW Paste Data Copied
Advertisement