MS sql 查询动态表名的方法

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 语句返回结果集