SQL Server中EXEC与SP_EXECUTESQL的不同之处

SQL Server提供了两种执行动态SQL语句的命令，分别是EXEC和SP_EXECUTESQL。我们先建立一个表，并添加一些数据进行演示：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)); INSERT INTO t_student VALUES(1, '张小红', 8, '育才小学', '一班', 92); INSERT INTO t_student VALUES(2, '王丽丽', 8, '育才小学', '一班', 90); INSERT INTO t_student VALUES(3, '张燕', 7, '云华小学', '二班', 86); INSERT INTO t_student VALUES(4, '刘华', 6, '云华小学', '二班', 85); EXEC命令可以用于执行存储过程或动态SQL语句。我们来看看怎么执行存储过程SP_GetStudent，该存储过程返回成绩大于90分的学生：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; 用EXEC命令调用存储过程的示例：DECLARE @return_value int, @OutNums int; EXEC @return_value = [dbo].[Sp_GetStudent] @Score = 90, @Nums = @OutNums OUTPUT;