SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

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

AskSQLTeam
Ask SQLTeam Question

USA
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

ag_sql
Starting Member

USA
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.

Aruna
Go to Top of Page

Jeff Moden
Aged Yak Warrior

USA
649 Posts

Posted - 02/28/2008 :  15:05:54  Show Profile  Reply with Quote
You said...
quote:
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

sameera
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

jsmith8858
Dr. Cross Join

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

http://weblogs.sqlteam.com/jeffs/archive/2007/10/18/sql-server-cross-apply.aspx


- Jeff
http://weblogs.sqlteam.com/JeffS
Go to Top of Page
  Previous Topic Topic Next Topic  
 New 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.07 seconds. Powered By: Snitz Forums 2000