July 27, 2005 at 2:00 pm
I have a stored procedure that includes an openquery for a linked server:
CREATE PROCEDURE sp_MPMS_GetShortTitle
@MPMS_NO numeric(6, 0)
AS
DECLARE @sql varchar(3000)
SET @sql =
'SELECT ShortTitle
FROM OPENQUERY (MPMS, ''select t047project.prjct_shrt_titl_tx AS ShortTitle from t047project where prjct_id = ' + Convert( varchar(20), @MPMS_NO ) + ' '' )'
EXEC( @sql )
GO
The query alwasy returns only one record so I wanted to create a function that would except a @MPMS_NO parameter and return a record. I was hoping to call this function from within another SQL statement, pass a field to it and get the result in the recordset, somehting like this:
SELECT table.field1, table.field2, myFunction(table.MPMS_NO) FROM...
Is it possible to do something like this? I'm struggling with syntax to get it done, since the finction must return a value but my in case there is only sql execution. Thanks!
July 28, 2005 at 1:50 am
OPENQUERY runs on the target server and returns a result set to the local server. If you want to join tables across servers (and I think that is what you are trying to do), then you need to use four part naming with a conventional join. Perhaps you can use OPENQUERY to return a set to a temporary table (or just as a derived table) and then join to that in the local database so as to reduce network traffic, which is the key consideration here. If you are going to have a function referenced in the OPENQUERY, then that function has to be on the target server.
July 28, 2005 at 5:14 am
You cannot execute dynamic sql within a body of function unless its an extended stored procedure..u can do something like this in the body of function..
return(SELECT lastname
FROM OPENQUERY
(LkServer2, 'select lastname from northwind.dbo.employees where firstname=''NANCY'' '))
if u see this, i have hardcoded the first name but i cannot pass it as a parameter because that will turn it into a dyna sql and will require EXEC which we cannot do in a function..
Anyone having different ideas, i wud be glad to hear
Regards,
Dilip
July 28, 2005 at 5:24 am
I was assuming that he needed to filter his results based on a table in the local database. Lets say that we need Nancies whose surnames match some list. Then you could do :
SELECT sl.SomeFields
FROM SurnameList sl
JOIN (OPENQUERY (LkServer2,
'select lastname
from northwind.dbo.employees
where firstname=''NANCY'' ')) oq
ON oq.lastname = sl.lastname
This way, we keep network traffic to a minimum.
July 28, 2005 at 5:34 am
But the catch is how to execute this in a function i.e. i dun wanna hardcode "Nancy" instead of that i want to use say @lastname now, here is the issue..we cannot use EXEC statement to execute this dynamic SQL within a function...hope im correct on this...i put in 1 hour but no luck 🙁
July 28, 2005 at 5:37 am
You can't use a function in the way that you want. Can you write the relevant values to a temporary table on the target server and then join to that in the OPENQUERY?
July 28, 2005 at 8:24 am
Yes, that's probably the only solution in this case: writing into a temp table. Thanks all for your replies!
July 28, 2005 at 7:39 pm
But u can't use a #temp or create table within function body..u will have to either go the stored proc way or create a 1 column paramater table if u wanna use it in function...best of lcuk 🙂
dilip
July 29, 2005 at 2:21 am
Hi,
did you try to create a view containing the OPENQUERY to the linked Server, and use the function to select from this view?
karl
Best regards
karl
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply