|
Subject:
|
User Defined Function
|
|
Posted By:
|
niravp
|
Post Date:
|
11/16/2004 2:38:48 PM
|
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
|
|
Reply By:
|
rgerald
|
Reply Date:
|
11/16/2004 2:46:49 PM
|
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
|
|
Reply By:
|
niravp
|
Reply Date:
|
11/16/2004 5:48:13 PM
|
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
|
|
Reply By:
|
om_prakash
|
Reply Date:
|
11/17/2004 12:09:07 AM
|
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
|
|
Reply By:
|
joefawcett
|
Reply Date:
|
11/17/2004 6:06:32 AM
|
quote:
Originally posted by niravp
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.
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)
|
|
Reply By:
|
niravp
|
Reply Date:
|
11/17/2004 1:27:23 PM
|
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
|
|
Reply By:
|
om_prakash
|
Reply Date:
|
11/20/2004 4:09:27 AM
|
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
|
|
Reply By:
|
niravp
|
Reply Date:
|
11/29/2004 1:18:54 PM
|
Thanks, it works.
quote:
Originally posted by om_prakash
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
|
