ALTER PROCEDURE [dbo].[sp_GetClassCountData]
@BatchId NVARCHAR(50),
@ExamId VARCHAR(100),
@ClassId VARCHAR(100),
@SubjectId NVARCHAR(50)
AS
BEGIN
DECLARE @TableName NVARCHAR(100),
@yxStartScore decimal(18,2),
@yxEndScore decimal(18,2),
@yxNum varchar(50),
@jgStartScore decimal(18,2),
@jgEndScore decimal(18,2),
@jgNum varchar(50)
SET @TableName='[Z_Exam_Subject_Count_'+@ExamId + ']';
select @yxStartScore=LineStart,@yxEndScore=LineEnd from AcademicGrade where LineLevel='A';
select @jgStartScore=LineStart,@jgEndScore=LineEnd from AcademicGrade where LineLevel='C';
--获得优秀人数
DECLARE @str2 NVARCHAR(MAX)='select @Nums= count(*) from '+@TableName+' where BatchId=@BatchId and ExamId=@ExamId and ClassId=@ClassId and SubjectId=@SubjectId and TotalScore >=@yxStartScore and TotalScore<=@yxEndScore'
PRINT(@str2)
exec SP_EXECUTESQL @str2,N'@Nums INT OUT,@BatchId NVARCHAR(50), @ExamId VARCHAR(100),@ClassId VARCHAR(100), @SubjectId NVARCHAR(50),
@yxStartScore decimal(18,2),@yxEndScore decimal(18,2)',@yxNum OUTPUT,@BatchId,@ExamId,@ClassId,@SubjectId,@yxStartScore,@yxEndScore
--N'@Nums INT OUT,@BatchId NVARCHAR(50), @ExamId VARCHAR(100),@ClassId VARCHAR(100), @SubjectId NVARCHAR(50),
@yxStartScore decimal(18,2),@yxEndScore decimal(18,2) 这些参数是查询所需参数，查询条件少的可以减少数量，@Nums INT OUT是返回值
--获得及格人数
DECLARE @str3 NVARCHAR(MAX)='select @Nums= count(*) from '+@TableName+' where BatchId=@BatchId and ExamId=@ExamId and ClassId=@ClassId and SubjectId=@SubjectId and TotalScore >=@jgStartScore and TotalScore<@jgEndScore'
PRINT(@str3)
exec SP_EXECUTESQL @str3,N'@Nums INT OUT,@BatchId NVARCHAR(50), @ExamId VARCHAR(100),@ClassId VARCHAR(100), @SubjectId NVARCHAR(50),
@jgStartScore decimal(18,2),@jgEndScore decimal(18,2)',@jgNum OUTPUT,@BatchId,@ExamId,@ClassId,@SubjectId,@jgStartScore,@jgEndScore
DECLARE @str NVARCHAR(MAX)='select count(*) as tatolNum,AVG(TotalScore) as avgs,max(TotalScore) as maxs,min(TotalScore) as mins,
case
when COUNT(*)=0 then ''0%''
else CONVERT(varchar(100), Round(convert(float,'+@yxNum+')/convert(float,COUNT(*))*100,2))+''%'' --两个数相除四舍五入保存两们小数后再添加百分号
end as yxl,
case
when COUNT(*)=0 then ''0%''
else CONVERT(varchar(100), Round(convert(float,'+@jgNum+')/convert(float,COUNT(*))*100,2))+''%''
end as jgl
from '+@TableName+' where BatchId='''+@BatchId+''' and ExamId='''+@ExamId+''' and ClassId='''+@ClassId+''' and SubjectId='''+@SubjectId+''''
PRINT(@str)
EXEC(@str)
END
-----------------------------------详细说明----------------------------------------
EXEC 执行拼接sql语句的时候不支持 嵌入式参数，如下：
DECLARE @OUT_Nums INT,@IN_Score INT,@Sql NVARCHAR(MAX) SET @IN_Score = 90 SET @sql = 'SELECT @Nums=COUNT(1) FROM t_student WHERE Score >= @Score' EXEC (@sql)
通过上面的代码发现，EXEC 执行拼接的SQL语句的时候，不支持内嵌参数，包括输入参数和输出参数。有的时候我们想把得到的count(*)传出来，用EXEC是不好办到的。接下来，再来看看SP_EXECUTESQL的使用：
二、SP_EXECUTESQL：
SP_EXECUTESQL 是在 SQL 2005中引入的新的系统存储过程，也是用来处理动态SQL 语句的。它比EXEC 更加灵活
SP_EXECUTESQL 支持内嵌参数：
先来看一下SP_EXECUTESQL的语法：
sp_executesql [ @stmt = ] stmt
[
{, [@params=] N'@parameter_name data_type [ OUT | OUTPUT ][,...n]' }
{, [ @param1 = ] 'value1' [ ,...n ] }
]
说明：
[ @stmt = ] stmt 包含 Transact-SQL 语句或批处理的 Unicode 字符串。stmt 必须是 Unicode 常量或 Unicode 变量。不允许使用更复杂的 Unicode 表达式（例如使用 + 运算符连接两个字符串）。不允许使用字符常量。如果指定了 Unicode 常量，则必须使用 N 作为前缀。例如，Unicode 常量 N'sp_who' 是有效的，但是字符常量 'sp_who' 则无效。字符串的大小仅受可用数据库服务器内存限制。在 64 位服务器中，字符串大小限制为 2 GB，即 nvarchar(max) 的最大大小。stmt 中包含的每个参数在 @params 参数定义列表和参数值列表中均必须有对应项
[ @params = ] N'@parameter_namedata_type[ ,... n ] ' 包含 stmt 中嵌入的所有参数定义的字符串。字符串必须是 Unicode 常量或 Unicode 变量。每个参数定义由参数名称和数据类型组成。n 是表示附加参数定义的占位符。在 stmt 中指定的每个参数必须在 @params 中定义。如果 stmt 中的 Transact-SQL 语句或批处理不包含参数，则不需要 @params。该参数的默认值为 NULL。
[ @param1 = ] 'value1'
参数字符串中定义的第一个参数的值。该值可以是 Unicode 常量，也可以是 Unicode 变量。必须为 stmt 中包含的每个参数提供参数值。如果 stmt 中的 Transact-SQL 语句或批处理没有参数，则不需要这些值。
[ OUT | OUTPUT ]
指示参数是输出参数。除非是公共语言运行 (CLR) 过程，否则 text、ntext 和 image 参数均可用作 OUTPUT 参数。使用 OUTPUT 关键字的输出参数可以为游标占位符，CLR 过程除外。
n 附加参数值的占位符。这些值只能为常量或变量，不能是很复杂的表达式（例如函数）或使用运算符生成的表达式。
返回代码值 :
0（成功）或非零（失败）
结果集：从生成 SQL 字符串的所有 SQL 语句返回结果集