1 ALTER PROC [dbo].[Student_Friend_Get] 2 @startRowIndexId INT, 3 @maxNumberRows INT, 4 @schoolId INT, 5 @gradeId INT, 6 @cId INT, 7 @keyWords NVARCHAR(100), 8 @userName VARCHAR(50) 9 AS 10 BEGIN 11 DECLARE @sqlfilter VARCHAR(max) 12 SET @sqlfilter = ' ' 13 IF(@schoolId <> -1) 14 SET @sqlfilter = @sqlfilter + ' tableu.SchoolId = ' + CAST(@schoolId AS VARCHAR(50)) + ' AND' 15 IF(@gradeId <> -1) 16 SET @sqlfilter = @sqlfilter + ' tableu.GradeId = ' + CAST(@gradeId AS VARCHAR(50)) + ' AND' 17 IF(@cId <> -1) 18 SET @sqlfilter = @sqlfilter + ' tableu.ClassId = ' + CAST(@cId AS VARCHAR(50)) + ' AND' 19 IF(@keyWords IS NOT NULL) 20 SET @sqlfilter = @sqlfilter + ' tableu.TrueName like ''%' + CAST(@keyWords AS VARCHAR(50)) + '%'' AND' 21 22 DECLARE @beg INT,@end INT 23 SET @beg = @startRowIndexId+1 24 SET @end = @startRowIndexId + @maxNumberRows 25 SET @sqlfilter = @sqlfilter + ' tableu.num BETWEEN ' +CAST( @beg AS VARCHAR(50)) + ' AND '+ CAST(@end AS VARCHAR(50)) 26 27 DECLARE @sqlmain VARCHAR(max) 28 SET @sqlmain = ' ' 29 SET @sqlmain = @sqlmain + ' SELECT * FROM 30 ( 31 SELECT ROW_NUMBER() OVER(ORDER BY cjs.UserName) AS num,CTA.TrueName, u.UserName, c.ClassName + '' (''+ CAST(YEAR(c.GradeUpdateTime) AS NVARCHAR(20))+''年)'' AS [ClassName],s.SchoolName,cjs.ApplyTime,g.GradeName,cjs.ApplyID,c.ClassId,g.GradeId,s.SchoolId 32 FROM PE_C_StudentJoinClass AS cjs 33 LEFT JOIN dbo.PE_SS_StudentClass AS c 34 ON cjs.ClassId = c.ClassId 35 LEFT JOIN dbo.PE_Users AS u 36 ON u.UserName = cjs.UserName 37 LEFT JOIN dbo.PE_SS_Grade g 38 ON g.GradeId = c.GradeId 39 LEFT JOIN dbo.PE_SS_School s 40 ON s.SchoolId = g.SchoolId 41 LEFT JOIN PE_Contacter CTA 42 ON cjs.UserName = CTA.UserName 43 WHERE ApplyID IN 44 ( 45 SELECT 46 MAX(cs1.ApplyID) AS [ApplyID] 47 FROM PE_C_StudentJoinClass AS cs1 48 CROSS JOIN dbo.PE_C_StudentJoinClass AS cs2 49 WHERE cs2.UserName = '''+ CAST(@userName AS VARCHAR(50))+ ''' AND cs1.UserName != ''' + CAST(@userName AS VARCHAR(50))+ ''' AND cs1.ClassId = cs2.ClassId AND cs1.Status = 1 50 GROUP BY cs1.UserName 51 ) 52 ) AS tableu WHERE ' 53 54 PRINT (@sqlmain + @sqlfilter) 55 EXEC (@sqlmain + @sqlfilter) 56 57 END 58 59 60 GO