国产高清在线免费观看-国产高清在线免费无码-国产高清在线男人的天堂-国产高清在线视频-国产高清在线视频精品视频-国产高清在线视频伊甸园

CREATE TABLE t_student(
 Id INT NOT NULL,
 Name NVARCHAR (10) NULL,
 Age TINYINT NULL,
 School NVARCHAR(20) NULL,
 Class NVARCHAR(10) NULL,
 Score FLOAT NULL,
 CONSTRAINT [PK_Student_Id] PRIMARY KEY CLUSTERED(Id)
)
GO
INSERT INTO t_student VALUES(1,'張小紅',8,'育才小學(xué)','一班',92)
INSERT INTO t_student VALUES(2,'王麗麗',8,'育才小學(xué)','一班',90)
INSERT INTO t_student VALUES(3,'張燕',7,'云華小學(xué)','二班',86)
INSERT INTO t_student VALUES(4,'劉華',6,'云華小學(xué)','二班',85)

CREATE PROCEDURE [dbo].[Sp_GetStudent]
    @Score FLOAT,
    @Nums INT OUTPUT 
AS
BEGIN
    SET NOCOUNT ON;
    SELECT * FROM t_student WHERE Score >=@Score
    SELECT @Nums=COUNT(1) FROM t_student WHERE Score >=@Score
    IF(@Nums>0)
     RETURN 1
    ELSE
     RETURN 0
END
GO

DECLARE @return_value int,
        @OutNums int
EXEC    @return_value = [dbo].[Sp_GetStudent] 
        @Score = 90,
        @Nums = @OutNums OUTPUT
SELECT  @OutNums as N'大于90分的人數(shù)'
SELECT  '返回值' = @return_value
GO

DECLARE @TableName NVARCHAR(50),@Sql NVARCHAR(MAX),@Score INT;
SET @TableName = 't_Student';
SET @Score = 90;
SET @sql = 'SELECT * FROM '+QUOTENAME(@TableName) +'WHERE Score >= '+CAST(@Score AS NVARCHAR(10))
EXEC (@sql);

--這是錯(cuò)誤的調(diào)用
EXEC ('SELECT * FROM '+QUOTENAME(@TableName) +'WHERE Score >= '+CAST(@Score AS NVARCHAR(10)));

DECLARE @TableName NVARCHAR(50),@Sql NVARCHAR(MAX),@Score INT
DECLARE @Sql2 NVARCHAR(MAX)
SET @TableName = 't_Student';
SET @Score = 90;
SET @sql = 'SELECT * FROM '+QUOTENAME(@TableName)
SET @Sql2=' WHERE Score >= '+CAST(@Score AS NVARCHAR(10))
EXEC (@sql+@sql2)

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)

通過上面的代碼發(fā)現(xiàn)，EXEC 執(zhí)行拼接的SQL語句的時(shí)候，不支持內(nèi)嵌參數(shù)，包括輸入?yún)?shù)和輸出參數(shù)。有的時(shí)候我們想把得到的count(*)傳出來，用EXEC是不好辦到的。接下來，再來看看SP_EXECUTESQL的使用：

二、SP_EXECUTESQL：

SP_EXECUTESQL 是在 SQL 2005中引入的新的系統(tǒng)存儲(chǔ)過程，也是用來處理動(dòng)態(tài)SQL 語句的。它比EXEC 更加靈活，首先也執(zhí)行一下第一次的拼接SQL語句：

DECLARE @TableName NVARCHAR(50),@Sql NVARCHAR(MAX),@Score INT;
SET @TableName = 't_Student';
SET @Score = 90;
SET @sql = 'SELECT * FROM '+QUOTENAME(@TableName) +'WHERE Score >= '+CAST(@Score AS NVARCHAR(10))
EXEC SP_EXECUTESQL @sql --注意這里沒有了()

執(zhí)行結(jié)果：

SP_EXECUTESQL 支持內(nèi)嵌參數(shù)：

先來看一下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 變量。不允許使用更復(fù)雜的 Unicode 表達(dá)式（例如使用 + 運(yùn)算符連接兩個(gè)字符串）。不允許使用字符常量。如果指定了 Unicode 常量，則必須使用 N 作為前綴。例如，Unicode 常量 N'sp_who' 是有效的，但是字符常量 'sp_who' 則無效。字符串的大小僅受可用數(shù)據(jù)庫服務(wù)器內(nèi)存限制。在 64 位服務(wù)器中，字符串大小限制為 2 GB，即 nvarchar(max) 的最大大小。stmt 中包含的每個(gè)參數(shù)在 @params 參數(shù)定義列表和參數(shù)值列表中均必須有對(duì)應(yīng)項(xiàng)

[ @params = ] N'@parameter_namedata_type[ ,... n ] ' 包含 stmt 中嵌入的所有參數(shù)定義的字符串。字符串必須是 Unicode 常量或 Unicode 變量。每個(gè)參數(shù)定義由參數(shù)名稱和數(shù)據(jù)類型組成。n 是表示附加參數(shù)定義的占位符。在 stmt 中指定的每個(gè)參數(shù)必須在 @params 中定義。如果 stmt 中的 Transact-SQL 語句或批處理不包含參數(shù)，則不需要 @params。該參數(shù)的默認(rèn)值為 NULL。

[ @param1 = ] 'value1' 

參數(shù)字符串中定義的第一個(gè)參數(shù)的值。該值可以是 Unicode 常量，也可以是 Unicode 變量。必須為 stmt 中包含的每個(gè)參數(shù)提供參數(shù)值。如果 stmt 中的 Transact-SQL 語句或批處理沒有參數(shù)，則不需要這些值。

[ OUT | OUTPUT ]

指示參數(shù)是輸出參數(shù)。除非是公共語言運(yùn)行 (CLR) 過程，否則 text、ntext 和 image 參數(shù)均可用作 OUTPUT 參數(shù)。使用 OUTPUT 關(guān)鍵字的輸出參數(shù)可以為游標(biāo)占位符，CLR 過程除外。

n 附加參數(shù)值的占位符。這些值只能為常量或變量，不能是很復(fù)雜的表達(dá)式（例如函數(shù)）或使用運(yùn)算符生成的表達(dá)式。

返回代碼值 : 

0（成功）或非零（失敗）

結(jié)果集：從生成 SQL 字符串的所有 SQL 語句返回結(jié)果集

看不懂沒有關(guān)系，通過例子就會(huì)非常明白的，依舊還執(zhí)行上面的 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 SP_EXECUTESQL @sql,N'@Nums INT OUT,@Score INT',@OUT_Nums OUTPUT,@IN_Score
SELECT @OUT_Nums AS '人數(shù)'

 執(zhí)行結(jié)果：

需要注意的是：

1、要求動(dòng)態(tài)Sql和動(dòng)態(tài)Sql參數(shù)列表必須是NVARCHAR

2、動(dòng)態(tài)Sql的參數(shù)列表與外部提供值的參數(shù)列表順序必需一致

3、一旦使用了 '@name = value' 形式之后，所有后續(xù)的參數(shù)就必須以 '@name = value' 的形式傳遞，比如：

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 SP_EXECUTESQL @stmt=@sql,@params=N'@Nums INT OUT,@Score INT',@Nums=@OUT_Nums OUTPUT,@Score=@IN_Score
SELECT @OUT_Nums AS '人數(shù)'

通過上面的例子已經(jīng)很清晰的表明了，在執(zhí)行動(dòng)態(tài)SQL 語句的時(shí)候，EXEC 和  SP_EXECUTESQL 的區(qū)別了，來總結(jié)一下：

1、 性能：

官方描述：sp_executesql stmt 參數(shù)中的 Transact-SQL 語句或批處理在執(zhí)行 sp_executesql 語句時(shí)才編譯。隨后，將編譯 stmt 中的內(nèi)容，并將其作為執(zhí)行計(jì)劃運(yùn)行。該執(zhí)行計(jì)劃獨(dú)立于名為 sp_executesql 的批處理的執(zhí)行計(jì)劃。sp_executesql 批處理不能引用調(diào)用 sp_executesql 的批處理中聲明的變量。sp_executesql 批處理中的本地游標(biāo)或變量對(duì)調(diào)用 sp_executesql 的批處理是不可見的。對(duì)數(shù)據(jù)庫上下文所做的更改只在 sp_executesql 語句結(jié)束前有效。如果只更改了語句中的參數(shù)值，則 sp_executesql 可用來代替存儲(chǔ)過程多次執(zhí)行 Transact-SQL 語句。因?yàn)?Transact-SQL 語句本身保持不變，僅參數(shù)值發(fā)生變化，所以 SQL Server 查詢優(yōu)化器可能重復(fù)使用首次執(zhí)行時(shí)所生成的執(zhí)行計(jì)劃。

說通俗一點(diǎn)就是：如果用 EXEC 執(zhí)行一條動(dòng)態(tài) SQL 語句，由于每次傳入的參數(shù)不一樣，所以每次生成的 @sql 就不一樣，這樣每執(zhí)行一次SQL SERVER 就必須重新將要執(zhí)行的動(dòng)態(tài) Sql 重新編譯一次 。但是SP_EXECUTESQL 則不一樣，由于將數(shù)值參數(shù)化，要執(zhí)行的動(dòng)態(tài) Sql 永遠(yuǎn)不會(huì)變化，只是傳入的參數(shù)的值在變化，那每次執(zhí)行的時(shí)候就不用重新編譯，速度和效率自然有所提升。

2、從上面的例子我們已經(jīng)能夠看出 SP_EXECUTESQL 命令比 EXEC 命令更靈活，因?yàn)樗峁┮粋€(gè)接口，該接口及支持輸入?yún)?shù)也支持輸出參數(shù)。

3、EXEC 執(zhí)行純動(dòng)態(tài)SQL，執(zhí)行時(shí)可能無法使用預(yù)編譯的執(zhí)行計(jì)劃，關(guān)鍵是不安全，可以導(dǎo)致 SQL 注入 ，而 SP_EXECUTESQL 執(zhí)行參數(shù)化動(dòng)態(tài) SQL ,執(zhí)行時(shí)能使用預(yù)編譯的執(zhí)行計(jì)劃，而且保存存儲(chǔ)過程時(shí)就可以確定可以使用的預(yù)編譯的執(zhí)行計(jì)劃，而且最重要的是“安全”，天然免疫SQL 注入