UDF with OpenQuery Parameters - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

UDF with OpenQuery Parameters

Author

Topic

Orange74
Starting Member

4 Posts

Posted - 2011-12-20 : 11:03:06

Hello everyone,

I am looking for a way to solve my problem. I have to make an openquery from SQL Server 2008 to Oracle, using parameters. Firt, I maid this « simplyfied » query that worked great:

DECLARE @Data TABLE
(
ID_Point INT,
DateTimeStamp Datetime,
Value FLOAT
)
DECLARE @ID_Point int
SET @ID_Point = 14561
DECLARE @DATE_START DATETIME
SET @DATE_START = '19930101'
DECLARE @DATE_END DATETIME
SET @DATE_END = '19941201'

DECLARE @sqlORACLE NVARCHAR(MAX)
DECLARE @sqlSQLSERVER NVARCHAR(MAX)

SET @sqlORACLE = 'SELECT ID_Point, DateTimeStamp, VALUE
FROM TABLEData WHERE ID_Point = :ID
AND (DateTimeStamp BETWEEN TO_DATE('''':DATE_START'''',''''YYYY-MM-DD HH24:MI'''')
AND TO_DATE('''':DATE_END'''',''''YYYY-MM-DD HH24:MI''''))'

SET @sqlORACLE = REPLACE(@sqlORACLE, ':ID', @ID_Point)
SET @sqlORACLE = REPLACE(@sqlORACLE, ':DATE_START', @DATE_START)
SET @sqlORACLE = REPLACE(@sqlORACLE, ':DATE_END', @DATE_END)

--SELECT @sqlORACLE

SET @sqlSQLSERVER = 'SELECT ID_Point, DateTimeStamp, VALUE
FROM OPENQUERY(ORACLE_SERVER, ''@sqlORACLE'')'
SET @sqlSQLSERVER = REPLACE(@sqlSQLSERVER, '@sqlORACLE', @sqlORACLE)

INSERT INTO @Data
exec sp_executesql @sqlSQLSERVER

SELECT * FROM @Data

Then I tried to transfert it into a UDF, but it won't work because I use a Exec command and it is not allowed.

How can I do the equivalent in a UDF?

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-20 : 11:14:51

why you need to do it inside UDF? why cant you make it a procedure instead?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Orange74
Starting Member

4 Posts

Posted - 2011-12-20 : 11:27:02

I use the UDF as query to return a table of 3 columns, like a simple select statement. The actual UDF fetch data from 4 differents Database, 3 SQL Servers and 1 oracle. I am open to any solution. How can I do this with a procedure?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-20 : 12:29:40

same as you do it with udf. only difference being you created in as a procedure using

CREATE PROC...

and finally return it as a resultset instead of table

SELECT col1,Col2,col3,....

and you can use the other logic in same way you've used now.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Orange74
Starting Member

4 Posts

Posted - 2011-12-20 : 12:39:24

Can you give me an examble of a return result set from a storeproc? I didn't thought that we could return data from sp.

Orange74
Starting Member

4 Posts

Posted - 2011-12-20 : 12:52:33

Got it. I only have to convert the rest of my UDF in a SP now. Gladly, I was not too advance in it...

Thank you very much!

Charles

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-20 : 12:55:49

you're wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources