How to pass parameter values to a T-SQL query

Question

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"

Answer 1

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.

Answer 2

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