How to pass parameter values to a T-SQL query

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.

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

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