CODEWITHSUNDEEP
sql-serverremote-server
Betite
Betite

Reputation: 115

Call remote function from View - SQL Server

I looking for a way to call REMOTE FUNCTION in SELECT clause in VIEW. Basically I'am trying to do somthing like this:

    CREATE VIEW [dbo].[View_Year_Forcast] AS
        (SELECT    BshForecast.ProjectId, 
                   BshForecast.JobTypeId,
                   Job_Type.NAME,          
                   ProjectName , 
                   ProjectManagerID,                       
                   ProjectManagerName , 


***EXEC @START_DATE  =[192.168.0.10].[LudanProjectManager].[dbo].[func_Project_Get_Start_Date] @PROJECT_ID as [Start_Date],***

                   StartBudget , 
                   AdditionalBudget , 
                   TotalBudget  , 
                   UsedBudget ,   
        FROM       BshForecast  INNER  JOIN Job_Type ON BshForecast.JobTypeId = ID                   
        WHERE   (dbo.BshForecast.Year = DATEPART(YYYY,GETDATE()))   
                AND (dbo.BshForecast.IsDeleted = 0) 
                AND (dbo.BshForecast.BranchId = 200)
                AND (dbo.BshForecast.Approved = 1) );

And what I'am trying to get is a view that the seven'th column will hold the start date of each project that will be evaluate from a function in remote server.

Upvotes: 1

Views: 2205

Answers (1)

Andomar
Andomar

Reputation: 238176

The only way I know of calling a remote function is openquery. But openquery only takes a string literal, so you have to wrap the call to openquery in exec.

Here's an example that creates a function and calls it through the linked server called "localhost".

use TestDatabase
if exists (select * from sys.objects where name = 'fn_twice')
    drop function fn_twice
go
create function dbo.fn_twice(@i int) returns int as begin return 2*@i end
go
declare @i int
set @i = 21

declare @func_sql nvarchar(max)
set @func_sql = 'select @result = a.result from openquery(localhost, ' +
        '''select TestDatabase.dbo.fn_twice(' + 
        cast(@i as varchar(12)) + ') as result'') a'

declare @result int
exec sp_executesql @func_sql, N'@result int output', @result output

-- The result of the function call is now available in @result, and
-- you can use it in a query.

Upvotes: 2

Related Questions