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.

 All Forums
 Site Related Forums
 Article Discussion
 Article: Returning Complex Data from User-Defined

Author  Topic 

Ask SQLTeam Question

0 Posts

Posted - 2007-06-11 : 22:32:28
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.

Starting Member

3 Posts

Posted - 2007-07-19 : 10:58:24
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 - 2008-02-28 : 15:05:54
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 Post

Posted - 2009-03-23 : 22:59:53
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 - 2009-03-25 : 08:54:11
read this for examples of using cross apply without a UDF:

- Jeff
Go to Top of Page

- Advertisement -