Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 Article: Returning Complex Data from User-Defined
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Ask SQLTeam Question

0 Posts

Posted - 06/11/2007 :  22:32:28  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
SQL Server User-Defined Functions (UDFs) can return either a single value or virtual tables. However, sometimes we might like for a User-Defined Function to simply return more than 1 piece of information, but an entire table is more than what we need. For example, suppose we want a function that parses a single varchar() containing a street address and returns:
  • Street Number
  • Street Name
  • Unit Number
In other words, it would accept "100 Main St #44" and return 3 distinct, separate values:
  • Street Number: "100"
  • Street Name: "Main St"
  • Unit Number: "#44"
Article link.

Edited by - jsmith8858 on 06/12/2007 13:45:22

Starting Member

3 Posts

Posted - 07/19/2007 :  10:58:24  Show Profile  Reply with Quote
I really like this article. But I have similar but different issue with UDF. I need to use a function that is on a different server to update a column value in another server.
Function is a scalar-valued and I am not able to figure out how to write the update statement.

This is what I have so far with errors:
update county -- county table is in server A, database A
set station = serverB.databaseB.dbo.function_name(county.line_id, county.begin_measure) where county.series_id in (select series_id from serverB.databaseB.dbo.series where col2 = -1)

I get invalid column name: <serverB>
Is it possible to call a remote function in any DML statement?
We have linked servers created, dynamic parameters enabled on the provider. Our DBA could not find any solution either.
The work around solution we have right now is to create a copy of the function in the local database and make the local function connect to the remote database. This is redundant and maintenance nightmare.
Hope someone out there has a solution.
Thanks in advance for any help I can get.

Go to Top of Page

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 02/28/2008 :  15:05:54  Show Profile  Reply with Quote
You said...
In addition to parsing email addresses, we can return multiple date characteristics all at once, split strings into a fixed number of segments, parse schemes and domains and paths from URLs, return extra bit columns to indicate errors or other flags, and so on. I know I’ve come across situations in the past where I’ve thought that returning a few extra columns in a single-value UDF would be helpful, but I didn’t want to forgo the benefits of set-based processing so other, more complicated techniques were necessary.

It would be nice if you provided some examples of those other things...

--Jeff Moden
Go to Top of Page

Starting Member

1 Posts

Posted - 03/23/2009 :  22:59:53  Show Profile  Reply with Quote
I came across the support for APPLY while looking over the features of SQL Compact. Googling for it led me here. But, I'm wondering since SQL Compact does not support code in the DB (UDF or SP), how is APPLY going to be useful?
Go to Top of Page

Dr. Cross Join

7423 Posts

Posted - 03/25/2009 :  08:54:11  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
read this for examples of using cross apply without a UDF:

- Jeff
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000