SQL Server在动态SQL中等同于Oracle绑定变量？ [英] What is the SQL Server equivalent of Oracle bind variables in dynamic SQL?

问题描述

在Oracle中，编写动态SQL时会执行以下操作：

In Oracle, when writing dynamic SQL one does something like this:

create or replace procedure myProc(n in number)
as
begin
  execute immediate
   'update myTable set myColumn = :n' 
   using n;
commit;
end;

然后神奇发生。 SQL Server中的等效概念/语法（如果有）是什么？ （顺便说一句，我正在使用SQL Server 2005）

And then 'magic happens'. What, if any, is the equivalent concept / syntax in SQL Server? (BTW I'm using SQL Server 2005)

推荐答案

您将使用 sp_executesql 。绑定的变量如下所示： @ var1 。

You would use sp_executesql. The bound variables look like this: @var1.

在下面的链接中，有一个针对标准Northwind的示例查询数据库：

From the below link, an example query against the standard Northwind database:

DECLARE @IntVariable int;
DECLARE @SQLString nvarchar(500);
DECLARE @ParmDefinition nvarchar(500);

/* Build the SQL string one time.*/
SET @SQLString =
     N'SELECT BusinessEntityID, NationalIDNumber, JobTitle, LoginID
       FROM AdventureWorks2008R2.HumanResources.Employee 
       WHERE BusinessEntityID = @BusinessEntityID';
SET @ParmDefinition = N'@BusinessEntityID tinyint';
/* Execute the string with the first parameter value. */
SET @IntVariable = 197;
EXECUTE sp_executesql @SQLString, @ParmDefinition,
                      @BusinessEntityID = @IntVariable;
/* Execute the same string with the second parameter value. */
SET @IntVariable = 109;
EXECUTE sp_executesql @SQLString, @ParmDefinition,
                      @BusinessEntityID = @IntVariable;

完整的详细信息和示例语法在以下链接中：

Full details and example syntax are at the following links:

http://msdn.microsoft.com/en-us/library/ms188001.aspx

http：// msdn .microsoft.com / en-us / library / ms175170.aspx

这篇关于SQL Server在动态SQL中等同于Oracle绑定变量？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！