openquery in a function with parameters help!! -- sql server programming

Steve Kass
7/26/2004 10:14:31 PM

Howard,

The query string in openquery has to be a literal string, and while in
a procedure, you could create the entire openquery query dynamically,
you can't in a table-valued function, because it would require you to
use EXEC. You can put the filter outside the openquery (first example
below), but then I think the entire table will be filtered on the client
regardless of the capabilities of the provider. Is using a 4-part name
instead of openquery an option (second example below)?

create function myFunc (
@var1 smallint,@var2 varchar(10)
) returns table as return
select * from openquery(
desktop,
'select * from Northwind..Orders') T
where EmployeeID = @var1
and CustomerID = @var2
go

select * from myFunc(1,'ERNSH')
go

drop function MyFunc
go

create function myFunc (
@var1 smallint,@var2 varchar(10)
) returns table as return
select * from desktop.Northwind.dbo.Orders
where EmployeeID = @var1
and CustomerID = @var2
go

select * from myFunc(1,'ERNSH')
go

drop function MyFunc
go

Steve Kass
Drew University

[quoted text, click to view]

Howard Carr
7/26/2004 11:47:28 PM

I have a linked server and I want to create a function that returns a table
from the results of openquery

I want to be able to pass variable to the openquery command i.e.

create function myFunc (@var1 smallint,@var2 vharcha(10)
returns table
as
return
(
select * from openquery(linkedServer, select * from table where
tableID=@var1 and name=@var2)
)

This does not work as variables are not allowed.

How would I do this?

Howard Carr
7/27/2004 3:10:51 AM

Thanks for you reply.
The problem with the first method, is as you say, the where clause is done
after all the information has been returned to the client.
The second one is one I also tried, but there is no indexes in the linked
server tables, therefore really slow queries.

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server programming

sql server programming : openquery in a function with parameters help!!