Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old November 16th, 2004, 03:38 PM
Authorized User
 
Join Date: Jul 2003
Location: , , .
Posts: 22
Thanks: 0
Thanked 0 Times in 0 Posts
Default User Defined Function

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

  #2 (permalink)  
Old November 16th, 2004, 03:46 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Naperville, IL, USA.
Posts: 196
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
  #3 (permalink)  
Old November 16th, 2004, 06:48 PM
Authorized User
 
Join Date: Jul 2003
Location: , , .
Posts: 22
Thanks: 0
Thanked 0 Times in 0 Posts
Default


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


  #4 (permalink)  
Old November 17th, 2004, 01:09 AM
Friend of Wrox
Points: 2,473, Level: 20
Points: 2,473, Level: 20 Points: 2,473, Level: 20 Points: 2,473, Level: 20
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: May 2004
Location: India
Posts: 642
Thanks: 0
Thanked 43 Times in 42 Posts
Default

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
  #5 (permalink)  
Old November 17th, 2004, 07:06 AM
joefawcett's Avatar
Wrox Author
Points: 9,763, Level: 42
Points: 9,763, Level: 42 Points: 9,763, Level: 42 Points: 9,763, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Exeter, , United Kingdom.
Posts: 3,074
Thanks: 1
Thanked 38 Times in 37 Posts
Default

Quote:
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)
  #6 (permalink)  
Old November 17th, 2004, 02:27 PM
Authorized User
 
Join Date: Jul 2003
Location: , , .
Posts: 22
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

  #7 (permalink)  
Old November 20th, 2004, 05:09 AM
Friend of Wrox
Points: 2,473, Level: 20
Points: 2,473, Level: 20 Points: 2,473, Level: 20 Points: 2,473, Level: 20
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: May 2004
Location: India
Posts: 642
Thanks: 0
Thanked 43 Times in 42 Posts
Default

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
  #8 (permalink)  
Old November 29th, 2004, 02:18 PM
Authorized User
 
Join Date: Jul 2003
Location: , , .
Posts: 22
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks, it works.

Quote:
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


Similar Threads
Thread Thread Starter Forum Replies Last Post
User Defined function in XSL1.0 elayaraja.s XML 1 July 22nd, 2008 03:24 AM
Go for View or User Defined function vinod_yadav1919 SQL Server 2000 1 December 12th, 2007 05:18 AM
calling the user defined function on click event CsharpHelp C# 1 June 6th, 2005 07:25 AM
Calling User Defined Function penta Access 5 March 4th, 2005 12:57 PM
Using a user defined type and the Split Function nikotromus Access VBA 2 August 20th, 2004 12:02 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.