Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
Password Reminder
Register
Register | FAQ | Members List | Calendar | 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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old November 16th, 2004, 02: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

Reply With Quote
  #2 (permalink)  
Old November 16th, 2004, 02: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
Reply With Quote
  #3 (permalink)  
Old November 16th, 2004, 05: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


Reply With Quote
  #4 (permalink)  
Old November 17th, 2004, 12: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
Reply With Quote
  #5 (permalink)  
Old November 17th, 2004, 06:06 AM
joefawcett's Avatar
Wrox Author
Points: 9,716, Level: 42
Points: 9,716, Level: 42 Points: 9,716, Level: 42 Points: 9,716, Level: 42
Activity: 9%
Activity: 9% Activity: 9% Activity: 9%
 
Join Date: Jun 2003
Location: Exeter, , United Kingdom.
Posts: 3,067
Thanks: 1
Thanked 37 Times in 36 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)
Reply With Quote
  #6 (permalink)  
Old November 17th, 2004, 01: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

Reply With Quote
  #7 (permalink)  
Old November 20th, 2004, 04: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
Reply With Quote
  #8 (permalink)  
Old November 29th, 2004, 01: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
Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

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 04: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 11:57 AM
Using a user defined type and the Split Function nikotromus Access VBA 2 August 20th, 2004 12:02 PM



All times are GMT -4. The time now is 05:18 AM.


Powered by vBulletin® Version 3.7.0
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.