title: How I can swap two values in same column with dynamic SQL?
body: <p>I am using SQL Server 2008.</p>
  
  <p>I have many tables, each table contains <strong>ID</strong> field and <strong>TheOrder</strong> field.</p>
  
  <p>I want to create a <strong>stored procedure</strong> which can swap values of two records in <strong>TheOrder</strong> field.</p>
  
  <p>I am getting the <strong>IDs</strong> of these records by <strong>ASP.NET</strong> code and pass them to the stored procedure.</p>
  
  <p>But because I want to execute this <strong>stored procedure</strong> on many tables, I have to use dynamic SQL and pass table name from <strong>ASP.NET</strong> code.</p>
  
  <p>My <strong>stored procedure</strong> code is:</p>
  
  <pre><code>ALTER PROCEDURE [dbo].[SwapOrders]
      @TblName nvarchar(20)
      , @ID1 int
      , @ID2 int
  AS
  BEGIN
      DECLARE @ID1Str nvarchar(5)
      SET @ID1Str = CAST(@ID1 AS nvarchar)
      DECLARE @ID2Str nvarchar(5)
      SET @ID2Str = CAST(@ID2 AS nvarchar)
      DECLARE @Order1 nvarchar(5)
      DECLARE @Order2 nvarchar(5)
      DECLARE @Cmd nvarchar(100)
      /* Get orders */
      SET @Cmd = 'SELECT @Order1 = [TheOrder] FROM ' + @TblName + ' WHERE ID = ' + @ID1Str
      EXECUTE sp_executesql @Cmd, "@Order1 nvarchar(5) OUTPUT", @Order1 = [TheOrder]
      SET @Cmd = 'SELECT @Order2 = [TheOrder] FROM ' + @TblName + ' WHERE ID = ' + @ID2Str
      EXECUTE sp_executesql @Cmd, "@Order2 nvarchar(5) OUTPUT", @Order2 = [TheOrder]
      /* Update orders */
      SET @Cmd = 'UPDATE ' + @TblName + ' SET TheOrder = @Order2 WHERE ID = ' + @ID1Str
      EXECUTE sp_executesql @Cmd, "@Order2 nvarchar(5)", @Order2
      SET @Cmd = 'UPDATE ' + @TblName + ' SET TheOrder = @Order1 WHERE ID = ' + @ID2Str
      EXECUTE sp_executesql @Cmd, "@Order1 nvarchar(5)", @Order1
  END
  GO
  </code></pre>
  
  <p>When I tested this code, two values of <strong>TheOrder</strong> field become <strong>NULL</strong>.</p>
  
  <p>What is the problem?</p>
  
  <p>Thanks very much for any help, and I am sorry for long question.</p>