[SOLVED] How to pass parameter values to a T-SQL query

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"

Solution

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.