CODEWITHSUNDEEP
sql-serveroracle-databaset-sqldynamic-sqlopenquery
Riser Of Evil
Riser Of Evil

Reputation: 19

Table-Based Function using variables and OpenQuery

I am attempting to query data from our Oracle server via our SQL server. To perform this in a thin-client manner, I am using OpenQuery. I would like to build a single table-based function that can be used to query various linked tables as needed. I can't quite figure out the syntax to make this work. Here is what I have so far. Any help is greatly appreciated!

CREATE FUNCTION [dbo].[fnTEST](
@Table  varchar (100),
@Fields varchar (1000),
@Condition  varchar(5000)
)

RETURNS 
@TEST TABLE()

AS

BEGIN
DECLARE @OPENQUERY nvarchar(4000);
DECLARE @TSQL nvarchar(4000);

SET @OPENQUERY = 'SELECT * FROM OPENQUERY([TEST-Link],'''
SET @TSQL = 'SELECT ' + @Fields + ' FROM TEST.' + @Table + ' WHERE ' + @Condition + ''')'

EXEC (@OPENQUERY+@TSQL) 
END;

The error I am currently getting is:

Msg 102, Level 15, State 1, Procedure fnTEST, Line 12 [Batch Start Line 7]
Incorrect syntax near ')'.

Highlighted at @TEST TABLE()

Upvotes: 1

Views: 718

Answers (2)

RBarryYoung
RBarryYoung

Reputation: 56725

This is all not recommended for a number of reasons, but here the big one is that, as indicated in the MS doc, you cannot use dynamic SQL from a user-defined function:

Before You Begin

Limitations and restrictions

  • ...

  • User-defined functions cannot make use of dynamic SQL or temp tables. Table variables are allowed.

  • ...

Here are some of the other problems with this approach:

  • Your dynamic SQL is injectable. You should never use dynamic SQL unless you understand what SQL Injection is and how to prevent it in your dynamic SQL code.
  • Using dynamic sql has potential security requirements and restrictions. In this case the dynamic SQL may not have the same rights as your account and may not be able to use an OPENQUERY.
  • The nature of Database and Server Trustworthy settings may block this anyway.
  • IMHO, OPENQUERY is not recommended (some disagree), and remote queries are better handled with Linked Servers and the Remote EXEC command.
  • You are trying to write a "Universal Query" here. Universal Queries are generally not a good idea and have security problems, even after you fix the SQL Inject issues. It's better to define the specific queries needed by your app and code them as stored procedures and/or fixed queries using parameters only for WHERE conditions.
  • A SQL Function is not the right place for all of this anyway. You should regard a SQL table function as akin to a View, but with parameters for your WHERE clause. You should not treat it as a way to magically do anything.

The way that I would do something link this is as follows:

  1. Define the explicit queries/datasets that your app needs from the Oracle Database.

  2. Write those queries as stored procedures, on the Oracle database.

  3. Setup a Linked Server definition in your SQL Server database to the Oracle database. Configure the security for each side appropriately.

  4. Write specific stored procedures on your SQL Server to call the corresponding procedures in the Oracle database. Use remote EXEC's to do this through the Linked Server definition.

(NOTE: Remote EXEC execution is done with the AT <linkedServer> clause).

Upvotes: 1

David Browne - Microsoft
David Browne - Microsoft

Reputation: 89006

Enable the linked server for rpc out and simplify this to

EXEC (@sql) at [TEST-Link]

Upvotes: 0

Related Questions