Geek Answers Handbook

What is the equivalent of SQL Server binding variables for Oracle in dynamic SQL?

In Oracle, when writing dynamic SQL, it 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 the "magic" happens. What, if any, is the equivalent concept / syntax in SQL Server? (BTW I am using SQL Server 2005)

+5
source share
2 answers

You would use sp_executesql. Related variables are as follows: @var1.

The link below shows an example query for 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 provided in the following links:

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

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

+9

sp_executeSQL, , , exec(), : SQL.

+3

More articles:


All Articles