提供了编程的基础技术教程

zl程序教程

您现在的位置是：首页 >  数据库

当前栏目

SQL中sp_executesql存储过程的使用帮助

存储SQL 使用 过程 帮助 sp executesql
2023-06-13 09:14:27 时间

摘自SQLserver帮助文档
对大家优查询速度有帮助!

建议使用sp_executesql而不要使用EXECUTE语句执行字符串。支持参数替换不仅使sp_executesql比EXECUTE更通用，而且还使sp_executesql更有效，因为它生成的执行计划更有可能被SQLServer重新使用。

自包含批处理

sp_executesql或EXECUTE语句执行字符串时，字符串被作为其自包含批处理执行。SQLServer将Transact-SQL语句或字符串中的语句编译进一个执行计划，该执行计划独立于包含sp_executesql或EXECUTE语句的批处理的执行计划。下列规则适用于自含的批处理：

  • 直到执行sp_executesql或EXECUTE语句时才将sp_executesql或EXECUTE字符串中的Transact-SQL语句编译进执行计划。执行字符串时才开始分析或检查其错误。执行时才对字符串中引用的名称进行解析。
  • 执行的字符串中的Transact-SQL语句，不能访问sp_executesql或EXECUTE语句所在批处理中声明的任何变量。包含sp_executesql或EXECUTE语句的批处理不能访问执行的字符串中定义的变量或局部游标。
  • 如果执行字符串有更改数据库上下文的USE语句，则对数据库上下文的更改仅持续到sp_executesql或EXECUTE语句完成。

    • 通过执行下列两个批处理来举例说明：

    /*Shownothavingaccesstovariablesfromthecallingbatch.*/
DECLARE@CharVariableCHAR(3)
SET@CharVariable="abc"
/*sp_executesqlfailsbecause@CharVariablehasgoneoutofscope.*/
sp_executesqlN"PRINT@CharVariable"
GO

/*Showdatabasecontextresettingaftersp_executesqlcompletes.*/
USEpubs
GO
sp_executesqlN"USENorthwind"
GO
/*Thisstatementfailsbecausethedatabasecontext
hasnowreturnedtopubs.*/
SELECT*FROMShippers
GO
    替换参数值

    sp_executesql支持对Transact-SQL字符串中指定的任何参数的参数值进行替换，但是EXECUTE语句不支持。因此，由sp_executesql生成的Transact-SQL字符串比由EXECUTE语句所生成的更相似。SQLServer查询优化器可能将来自sp_executesql的Transact-SQL语句与以前所执行的语句的执行计划相匹配，以节约编译新的执行计划的开销。

    使用EXECUTE语句时，必须将所有参数值转换为字符或Unicode并使其成为Transact-SQL字符串的一部分：

    DECLARE@IntVariableINT
DECLARE@SQLStringNVARCHAR(500)
/*Buildandexecuteastringwithoneparametervalue.*/
SET@IntVariable=35
SET@SQLString=N"SELECT*FROMpubs.dbo.employeeWHEREjob_lvl="+
CAST(@IntVariableASNVARCHAR(10))
EXEC(@SQLString)
/*Buildandexecuteastringwithasecondparametervalue.*/
SET@IntVariable=201
SET@SQLString=N"SELECT*FROMpubs.dbo.employeeWHEREjob_lvl="+
CAST(@IntVariableASNVARCHAR(10))
EXEC(@SQLString)

    如果语句重复执行，则即使仅有的区别是为参数所提供的值不同，每次执行时也必须生成全新的Transact-SQL字符串。从而在下面几个方面产生额外的开销：

  • SQLServer查询优化器具有将新的Transact-SQL字符串与现有的执行计划匹配的能力，此能力被字符串文本中不断更改的参数值妨碍，特别是在复杂的Transact-SQL语句中。
  • 每次执行时均必须重新生成整个字符串。
  • 每次执行时必须将参数值（不是字符或Unicode值）投影到字符或Unicode格式。

    • sp_executesql支持与Transact-SQL字符串相独立的参数值的设置：

    DECLARE@IntVariableINT
DECLARE@SQLStringNVARCHAR(500)
DECLARE@ParmDefinitionNVARCHAR(500)

/*BuildtheSQLstringonce.*/
SET@SQLString=
N"SELECT*FROMpubs.dbo.employeeWHEREjob_lvl=@level"
/*Specifytheparameterformatonce.*/
SET@ParmDefinition=N"@leveltinyint"

/*Executethestringwiththefirstparametervalue.*/
SET@IntVariable=35
EXECUTEsp_executesql@SQLString,@ParmDefinition,
@level=@IntVariable
/*Executethesamestringwiththesecondparametervalue.*/
SET@IntVariable=32
EXECUTEsp_executesql@SQLString,@ParmDefinition,
@level=@IntVariable

    此sp_executesql示例完成的任务与前面的EXECUTE示例所完成的相同，但有下列额外优点：

  • 因为Transact-SQL语句的实际文本在两次执行之间未改变，所以查询优化器应该能将第二次执行中的Transact-SQL语句与第一次执行时生成的执行计划匹配。这样，SQLServer不必编译第二条语句。
  • Transact-SQL字符串只生成一次。
  • 整型参数按其本身格式指定。不需要转换为Unicode。
    •  

    说明  为了使SQLServer重新使用执行计划，语句字符串中的对象名称必须完全符合要求。

    重新使用执行计划

    在SQLServer早期的版本中要重新使用执行计划的唯一方式是，将Transact-SQL语句定义为存储过程然后使应用程序执行此存储过程。这就产生了管理应用程序的额外开销。使用sp_executesql有助于减少此开销，并使SQLServer得以重新使用执行计划。当要多次执行某个Transact-SQL语句，且唯一的变化是提供给该Transact-SQL语句的参数值时，可以使用sp_executesql来代替存储过程。因为Transact-SQL语句本身保持不变仅参数值变化，所以SQLServer查询优化器可能重复使用首次执行时所生成的执行计划。

    下例为服务器上除四个系统数据库之外的每个数据库生成并执行DBCCCHECKDB语句：

    USEmaster
GO
SETNOCOUNTON
GO
DECLAREAllDatabasesCURSORFOR
SELECTnameFROMsysdatabasesWHEREdbid>4

OPENAllDatabases

DECLARE@DBNameVarNVARCHAR(128)
DECLARE@StatementNVARCHAR(300)

FETCHNEXTFROMAllDatabasesINTO@DBNameVar
WHILE(@@FETCH_STATUS=0)
BEGIN
PRINTN"CHECKINGDATABASE"+@DBNameVar
SET@Statement=N"USE"+@DBNameVar+CHAR(13)
+N"DBCCCHECKDB("+@DBNameVar+N")"
EXECsp_executesql@Statement
PRINTCHAR(13)+CHAR(13)
FETCHNEXTFROMAllDatabasesINTO@DBNameVar
END

CLOSEAllDatabases
DEALLOCATEAllDatabases
GO
SETNOCOUNTOFF
GO

    当目前所执行的Transact-SQL语句包含绑定参数标记时，SQLServerODBC驱动程序使用sp_executesql完成SQLExecDirect。但例外情况是sp_executesql不用于执行中的数据参数。这使得使用标准ODBC函数或使用在ODBC上定义的API（如RDO）的应用程序得以利用sp_executesql所提供的优势。定位于SQLServer2000的现有的ODBC应用程序不需要重写就可以自动获得性能增益。有关更多信息，请参见使用语句参数。

    用于SQLServer的MicrosoftOLEDB提供程序也使用sp_executesql直接执行带有绑定参数的语句。使用OLEDB或ADO的应用程序不必重写就可以获得sp_executesql所提供的优势。

    1、执行带输出参数的组合sql

    declare@Dsqlnvarchar(1000),
      @Namevarchar(50),
      @TablePrimaryvarchar(50),
      @TableNamevarchar(50),
      @ASCint

     set@TablePrimary="ID";
     set@TableName="fine";
     set@ASC=1;

     set@Dsql=N"select@Name="+@TablePrimary+N"from"+@TableName+N"orderby"+@TablePrimary+(case@ASCwhen"1"thenN"DESC"ELSEN"ASC"END)
    print@Dsql

    SetRowcount7
       execsp_executesql@Dsql,N"@Namevarchar(50)output",@Nameoutput
    print@Name
    SetRowcount0

    2、执行带输入参数的组合sql

    DECLARE@IntVariableINT
DECLARE@SQLStringNVARCHAR(500)
DECLARE@ParmDefinitionNVARCHAR(500)

/*BuildtheSQLstringonce.*/
SET@SQLString=
N"SELECT*FROMpubs.dbo.employeeWHEREjob_lvl=@level"
/*Specifytheparameterformatonce.*/
SET@ParmDefinition=N"@leveltinyint"

/*Executethestringwiththefirstparametervalue.*/
SET@IntVariable=35
EXECUTEsp_executesql@SQLString,@ParmDefinition,
@level=@IntVariable
/*Executethesamestringwiththesecondparametervalue.*/
SET@IntVariable=32
EXECUTEsp_executesql@SQLString,@ParmDefinition,
@level=@IntVariable

    相关文章