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 語句返回結果集