SQL Server 中动态 SQL 中 Oracle 绑定变量的等价物是什么？

发布于 2024-11-27 05:15:37 字数 281 浏览 0 评论 0原文

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

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）

原文

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;

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

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

只是偏爱你 2024-12-04 05:15:37

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

下面的链接是针对标准 Northwind 数据库的示例查询：

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;

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

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

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

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

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

回复收藏 0 原文