6

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"
OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
David.Chu.ca
  • 37,408
  • 63
  • 148
  • 190

3 Answers3

19

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.

Jim Counts
  • 12,535
  • 9
  • 45
  • 63
  • 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 Feb 27 '09 at 06:38
  • SET @sql = 'select ? = count(*) from owner.mytable'; exec (@sql, @countVal output) at oracleServer -- failure for Oracle case – David.Chu.ca Feb 27 '09 at 06:40
  • The problem with this approach is that you can not return the result set from within another Stored Procedure...without creating a temp table (not feasible with a dynamic query) ...isn't there a function that does this? – Serj Sagan Jan 14 '15 at 16:18
1

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
John Sansom
  • 41,005
  • 9
  • 72
  • 84
0

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

Edit: Don't do the above. Use parameterized sql statements.

shahkalpesh
  • 33,172
  • 3
  • 63
  • 88
  • Input parameter is fine in this way, but how about the result? – David.Chu.ca Feb 27 '09 at 06:05
  • You might know by now, but for anyone reading this, the main reason I know of, is to avoid [SQL injections](https://owasp.org/www-community/attacks/SQL_Injection). Assume @id is text, not an integer and a hacker could set its value, setting it to `'1''; insert into users (name, is_admin) values (''Hacked!'', 1)--'` might succeed. – R. Schreurs Nov 24 '22 at 15:47
  • 1
    @R.Schreurs: Agree, that was in 2009. I may not have thought of it as anyone would in 2022 & I dont come back to my answers to correct it unless it is commented upon. Thank you. – shahkalpesh Nov 26 '22 at 14:45