• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 262
  • Last Modified:

Explicit datasource for User Defined Function

Im trying to use a function with two different data sources. For example I have a user defined function called GetNewCustomer. Lets say for example sake one data source is our New York customers and one is for California Customers. I would expect that I could explicitly declare one function with the NY data source and call the other one with the California data source like this:


Yet when run in a query or spreadsheet I just return whichever one is the active data source its like the declaration on the data source is ignored. Any suggestions?
1 Solution
I don't get your 100% but I guess you can create two schema (owner) and create two function with each schema and call it in the way you want.
I think you will have to pass the datasourcename as a parameter to the function and use dynamic SQL to change datasource names. Something like this maybe:
Select * From CustomerDB.dbo.GetNewCustomer('california')
And in the Fucntion you can have something like this:
ALTER FUNCTION GetNewCustomer(@DS varchar(50))
   RETURNS Table
DECLARE @SQL varchar(250)
SET @SQL = 'Select * From '+ @DS +'.CustomerDB.dbo.TableName'
I am assuming Data Source name is another SQL Server
keymarkITAuthor Commented:
Pssandhu, Im going to give this a try. This seems to be the logical approach. I will get back to you after testing.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now