How to pass parameter values to a T-SQL query

I am using the following T-SQL query in SQL server 2005 (Management Studio IDE):

DECLARE @id int;
DECLARE @countVal int;
DECLARE @sql nvarchar(max);
SET @id = 1000;
SET @sql = 'SELECT COUNT(*) FROM owner.myTable WHERE id = @id';
EXEC (@sql) AT oracleServer -- oracleServer is a lined server to Oracle

I am not sure how to pass the input parameter @id to the EXEC query, and pass the count result out to @countVal. I saw some examples for Microsoft SQL server like:

EXEC (@sql, @id = @id)

I tried this for Oracle but I got a statement error:

OLE DB provider "OraOLEDB.Oracle" for linked server "oracleServer" 
returned message "ORA-00936: missing expression"

From stackoverflow

• I don't know why you are pass id separately.

  You could do the following
  SET @sql = 'SELECT COUNT(*) FROM owner.myTable WHERE id = ' + @id

  David.Chu.ca : Input parameter is fine in this way, but how about the result?

• Try this:

  EXEC sp_executesql @sql, N'@id int', @id
  

  More info at this great article: http://www.sommarskog.se/dynamic_sql.html

  ---

  As for the output, your SELECT needs to look something like this:

  SELECT @countVal = COUNT(id) FROM owner.myTable WHERE id = @id
  

  I'm selecting 'id' instead of '*' to avoid pulling unnecessary data...

  Then your dynamic sql should be something like this:

  EXEC sp_executesql @sql, 
                     N'@id int, @countVal int OUTPUT', 
                     @id, 
                     @countVal OUTPUT
  

  This example is adapted from the same article linked above, in the section sp_executesql.

  ---

  As for your Oracle error, you will need to find out the exact SQL that sp_executesql is sending to Oracle. If there is a profiler or query log in Oracle, that may help. I have limited experience with Oracle, but that would be the next logical step for troubleshooting your problem.

  David.Chu.ca : The EXEC() example does not work with Oracle, very sad. If not parameters in @sql, it runs fine. I have no clue to use pass-through query for Oracle db with output parameter back to SQL.

  David.Chu.ca : SET @sql = 'select ? = count(*) from owner.mytable'; exec (@sql, @countVal output) at oracleServer -- failure for Oracle case

• The quick and dirty way is to just build the string before using the EXEC statement, however this is not the recommended practice as you may open yourself up to SQL Injection.

  DECLARE @id int;
  DECLARE @countVal int;
  DECLARE @sql nvarchar(max);
  SET @id = 1000;
  SET @sql = 'SELECT COUNT(*) FROM owner.myTable WHERE id = ' + @id 
  EXEC (@sql) AT oracleServer -- oracleServer is a lined server to Oracle
  

  The correct way to do this is to use the system stored procedure sp_executesql as detailed by magnifico, and recommended by Microsoft in Books Online is:

  EXEC sp_executesql @sql, N'@id int', @id