Hi!

I'm trying to write a User Defined Function on SQL Server, and the function need to connect to another database to get some values. Is this possible? Can a function in one database communicate to another database on the same server?

Or, is there a possiblity of calling a user defined function on one database from another database? If so could you please provide an example.

Thank you,

Nirav

Yes, getting data from another database on the same server is very simple. When you reference the table, add the db name to the beginning of the reference.

[db-name].[owner].[table-name].[field-name]



Rand

Thanks, Rand

That helps. I'm still not able to make my function work. I was trying to write a function to get a value from a table and return that value. Here's what I did:

CREATE FUNCTION dbo.getProgramCode
( @id numeric )
RETURNS table
AS
RETURN SELECT [INquiry].[dbo].[tblAcadProg].[acad_prog]
from [INquiry].[dbo].[tblAcadProg]
Where [INquiry].[dbo].[tblAcadProg].[acad_prog_id]= @id

I don't want a table datatype returned. A varchar would do coz there will always be just one value. I could not figure out how I can do it. And when I try to reference this function I get "Invalid object name" error.

Do you have idea where I'm going wrong.

Nirav

CREATE FUNCTION dbo.getProgramCode
( @id numeric )
RETURNS table
AS
RETURN SELECT acad_prog
from [INquiry].[dbo].[tblAcadProg]
Where acad_prog_id]= @id

This will return the table.

Om Prakash

Change RETURNS TABLE to RETURNS VARCHAR, if your actual column is not a varchar you may need to cast it in the select statement.



--

Joe (Microsoft MVP - XML)

Could not make the function work with returning a varchar. Tried replacing table with varchar but I get error.

CREATE FUNCTION dbo.getProgramCode
( @id numeric )
RETURNS varchar
AS
RETURN SELECT [INquiry].[dbo].[tblAcadProg].[acad_prog]
from [INquiry].[dbo].[tblAcadProg]
Where [INquiry].[dbo].[tblAcadProg].[acad_prog_id]= @id

Is this code snippet correct? The column I'm selecting is varchar as well.

Thanks,

Nirav

Please try this:

CREATE FUNCTION dbo.getProgramCode
( @id numeric )
RETURNS varchar(1000)
AS
BEGIN
DECLARE @TEMP VARCHAR(1000)
SELECT @TEMP = [INquiry].[dbo].[tblAcadProg].[acad_prog]
from [INquiry].[dbo].[tblAcadProg]
Where [INquiry].[dbo].[tblAcadProg].[acad_prog_id]= @id
RETURN(@TEMP)
END


Om Prakash

Thanks, it works.