title: Execute a stored procedure in another database with params
body: <p>I need to get the column names of a table that's located in another database. The following script works for the active database but I need to run it against another database in the same server instance:</p>
  
  <pre><code>SELECT @ColumnList = 
      CASE
          WHEN @ColumnList IS NULL THEN name
          WHEN @ColumnList IS NOT NULL THEN @ColumnList + ',' + name
      END
  FROM sys.columns
  WHERE object_id = Object_Id(@TableName);
  </code></pre>
  
  <p>Here's the issue... the database isn't known at compile time. Its passed into a stored procedure at runtime. So I see no alternative but to use dynamic sql. In the past I've tried using <code>Use [DBName]</code> in a dynamic sql script but always ran into problems until I realized I could do this:</p>
  
  <pre><code>SET @SQL = 'SELECT Foo FROM Bar'
  SET @sp_executesql = quotename(@DatabaseName) + '..sp_executesql'
  EXECUTE @sp_executesql @SQL
  </code></pre>
  
  <p>But I'm having difficulty figuring out how to do this with the script I mentioned above. My first attempt looked like:</p>
  
  <pre><code>-- @DatabaseName and @TableName are parameters of the 
  -- stored procedure containing this script
  
  DECLARE @ColumnList nvarchar(max),
          @SQL nvarchar(max),
          @sp_executesql nvarchar(max) = quotename(@DatabaseName) + '..sp_executesql';
  SET @SQL = 
  'SELECT @ColumnList = 
      CASE
          WHEN @ColumnList IS NULL THEN name
          WHEN @ColumnList IS NOT NULL THEN @ColumnList + '','' + name
      END
  FROM sys.columns
  WHERE object_id = Object_Id(@TableName);'
  
  EXECUTE @sp_executesql @SQL,
          N'@ColumnList = nvarchar(max) OUT, @TableName = sysname',
          @ColumnList, @TableName
  </code></pre>
  
  <p>But when it runs it doesn't interpret <code>@ColumnList</code> as a valid variable. What am I missing?</p>