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)
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
sp_executeSQL is probably the closest, there is also exec(), also mustread: The Curse and Blessings of Dynamic SQL.
