sqlserver 存储过程中拼接sql语句 动态执行

ALTER PROC [dbo].[Student_Friend_Get]
        @startRowIndexId INT,
        @maxNumberRows  INT,
        @schoolId  INT,
        @gradeId  INT,
        @cId  INT,
        @keyWords NVARCHAR(100),
        @userName VARCHAR(50)
         AS
                BEGIN
                    DECLARE @sqlfilter  VARCHAR(max)
                    SET @sqlfilter = ' '
                    IF(@schoolId <> -1)
                                SET @sqlfilter = @sqlfilter + '   tableu.SchoolId =  ' + CAST(@schoolId AS VARCHAR(50)) + ' AND'
                        IF(@gradeId <> -1)
                                SET @sqlfilter =  @sqlfilter +  '  tableu.GradeId =  ' + CAST(@gradeId AS VARCHAR(50)) + ' AND'
                        IF(@cId <> -1)
                                SET @sqlfilter =  @sqlfilter +  '  tableu.ClassId =  ' + CAST(@cId AS VARCHAR(50)) + ' AND'
                        IF(@keyWords IS NOT NULL)
                                SET @sqlfilter =  @sqlfilter + '   tableu.TrueName like  ''%' + CAST(@keyWords AS VARCHAR(50))         + '%''  AND'
                         
                        DECLARE @beg INT,@end INT
                        SET @beg =  @startRowIndexId+1
                        SET @end =  @startRowIndexId + @maxNumberRows
                        SET @sqlfilter = @sqlfilter +  '  tableu.num  BETWEEN  ' +CAST( @beg AS VARCHAR(50)) + ' AND '+ CAST(@end  AS VARCHAR(50))
                         
                        DECLARE @sqlmain  VARCHAR(max)
                        SET @sqlmain = ' '
                        SET @sqlmain = @sqlmain  +  ' SELECT * FROM
                        (
                            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
                                        FROM PE_C_StudentJoinClass AS cjs
                                        LEFT JOIN dbo.PE_SS_StudentClass AS c
                                        ON cjs.ClassId = c.ClassId
                                        LEFT JOIN dbo.PE_Users AS u
                                        ON u.UserName = cjs.UserName
                                        LEFT JOIN dbo.PE_SS_Grade g
                                        ON g.GradeId = c.GradeId
                                        LEFT JOIN dbo.PE_SS_School s
                                        ON s.SchoolId = g.SchoolId
                                        LEFT JOIN PE_Contacter CTA
                                        ON cjs.UserName = CTA.UserName
                                        WHERE ApplyID IN
                                        (
                                                SELECT  
                                                MAX(cs1.ApplyID) AS [ApplyID]
                                                FROM PE_C_StudentJoinClass AS cs1
                                                CROSS JOIN dbo.PE_C_StudentJoinClass AS cs2
                                                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
                                                GROUP BY cs1.UserName
                                        )
                                ) AS tableu WHERE '
                                 
                                PRINT (@sqlmain + @sqlfilter)
                            EXEC (@sqlmain + @sqlfilter)
 
                END
 
 
GO

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏测试开发架构之路

MySQL实现全关联 full outer join

SQL LEFT JOIN 关键字 LEFT JOIN 关键字会从左表 (table_name1) 那里返回所有的行,即使在右表 (table_name2) 中...

32330
来自专栏个人随笔

MySQL高级查询

 高级查询     关键字书写顺序  关键字执行顺序 select:投影结果       1    5 from:定位到表             2   ...

82390
来自专栏james大数据架构

SQL常用数据库结构升级语句

修改视图 --SQL Server 2005 GO IF EXISTS (SELECT * FROM sys.views WHERE object_id ...

24490
来自专栏程序猿

4.3.4.7 Pattern Matching

MySQL provides standard SQL pattern matching as well as a form of pattern matchi...

31460
来自专栏Hadoop数据仓库

Oracle 中 table 函数的应用

        表函数可接受查询语句或游标作为输入参数,并可输出多行数据。该函数可以平行执行,并可持续输出数据流,被称作管道式输出。应用表函数可将数据转换分阶段...

27080
来自专栏测试开发架构之路

分分钟搞懂union与union all

SQL UNION 操作符 UNION 操作符用于合并两个或多个 SELECT 语句的结果集。 请注意,UNION 内部的 SELECT 语句必须拥有相同数量的...

36090
来自专栏AhDung

【SQL】统计所有表的行数

原理:遍历所有用户表,用sp_spaceused过程分别获取每张表的行数并写入临时表,最后返回临时表

26320
来自专栏Netkiller

MySQL·身份证校验

本文节选自《Netkiller Architect 手札》 4.17. 数据检查 4.17.1. 身份证校验 该函数能够检查身份证号码是否正确 CREATE D...

45050
来自专栏技术碎碎念

T-SQL 查询、修改数据表

T-SQL修改表数据 INSERT语句 语法: INSERT [TOP(expression) [PERCENT]] [INTO] ...

48140
来自专栏电光石火

导出表结构(数据字典)

-- 数据字典 SELECT (case when a.colorder=1 then d.name else '' end)表名, a.co...

20460

扫码关注云+社区

领取腾讯云代金券