建议您在执行字符串时,使用 sp_executesql 存储过程而不要使用 EXECUTE 语句。由于此存储过程支持参数替换,因此 sp_executesql 比 EXECUTE 的功能更多;由于 SQL Server 更可能重用 sp_executesql 生成的执行计划,因此 sp_executesql 比 EXECUTE 更有效
下面是一个例子
CREATE PROCEDURE [dbo].[P_PCT_SP_EXECUTESQL] -- Add the parameters for the stored procedure here @EmployeeName nvarchar(50), @CreateUser nvarchar(50), @SortField nvarchar(50), @SortDir nvarchar(50) AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for procedure here declare @sql nvarchar(1000) set @sql = 'select * from employee where EmployeeName = @EmployeeName and CreateUser = @CreateUser order by ' + @SortField + ' ' + @SortDir exec sp_executesql @sql, N'@EmployeeName nvarchar(50),@CreateUser nvarchar(50),@SortField nvarchar(50),@SortDir nvarchar(50)', @EmployeeName,@CreateUser,@SortField,@SortDir END GO