getting multivalue from a function

Question

Post reply

getting multivalue from a function

ashkan sirous

SSCrazy

Points: 2733
More actions
November 29, 2011 at 6:29 am

#390981

Dear DBA's,
I have a giant query which joins lots of tables.
I have something like this:
select ..., getSelectedCol1(..someInput) , getSelectedCol2(..someInput) ,...
from....
both function acts the same way but returns 2 different columns, Is there any way to avoid Functions like this and call only one function?
I mean something like this:
select ... getSelectedCol1_2(..someInput)...
from....
Best Regards,
Ashkan

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 1

Try Table-Valued User-Defined Functions

http://msdn.microsoft.com/en-us/library/ms191165.aspx

ashkan sirous SSCrazy Points: 2733 More actions · Answer 2

Thanks Dev For Fast Reply, It is Great , but how can I use it in My select Query?

It should be a part of that...

Best Regards,
Ashkan

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 3

ashkan siroos (11/29/2011)
Thanks Dev For Fast Reply, It is Great , but how can I use it in My select Query?
It should be a part of that...

in FROM Clause.

Example:

USE AdventureWorks2008R2;

GO

SELECT ContactID, FirstName, LastName, JobTitle, ContactType

FROM dbo.ufnGetContactInformation(1209);

GO

SELECT ContactID, FirstName, LastName, JobTitle, ContactType

FROM dbo.ufnGetContactInformation(5);

GO

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

Try using Cross/Outer Apply.

You can easily convert the content into an inline TVF.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 5

I would generally go with JOIN and CROSS APPLY to solve these types of issues. Assuming you can do a single statement table valued function, that might work too. For very large queries with data amounts greater than a handful of rows, avoid multi-statement table valued functions like the plague.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

ashkan sirous SSCrazy Points: 2733 More actions · Answer 6

Dear Dev,

Thanks a lot, I've seen the link you sent , I know how to select data from that function, I don't know how to use it like a function without joining it with other tables.

Best Regards,
Ashkan

Sean Lange SSC Guru Points: 286590 More actions · Answer 7

How complicated is the function? could you just remove the function entirely?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 8

ashkan siroos (11/29/2011)
Dear Dev,
Thanks a lot, I've seen the link you sent , I know how to select data from that function, I don't know how to use it like a function without joining it with other tables.

Example:

SELECT D.deptid, D.deptname, D.deptmgrid

,ST.empid, ST.empname, ST.mgrid

FROM Departments AS D

CROSS APPLY fn_getsubtree(D.deptmgrid) AS ST;

For More

http://technet.microsoft.com/en-us/library/ms175156.aspx

ashkan sirous SSCrazy Points: 2733 More actions · Answer 9

Dear Dev, Grant Fritchey and ChrisM@Work ,

Thanks for reply,

I've read some articles about cross apply, every body said that cross apply is less efficient.

I need something to work like a simple function.

Best Regards,
Ashkan

ashkan sirous SSCrazy Points: 2733 More actions · Answer 10

Dear Sean Lange

Thanks for reply, If there is no other way, I will use joins... thanks alot...:(

Best Regards,
Ashkan

Sean Lange SSC Guru Points: 286590 More actions · Answer 11

ashkan siroos (11/29/2011)
Dear Sean Lange
Thanks for reply, If there is no other way, I will use joins... thanks alot...:(

Well if the function itself is fairly simple you may get a nice performance boost by just creating your joins again instead of the function. There are tons of factors into the performance aspect though. If the cross apply works and performance is not an issue that is probably simpler to code. 😛

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

ashkan sirous SSCrazy Points: 2733 More actions · Answer 12

Dear Sean Lange,

Just another Issue is bothering me...

How can I implement top 1 in my joins?

In my function I have a select like this:

set @retval =(select top 1 col1 from tbl1 inner join tbl2 ... inner join tbln

order by ... desc)

Now , If I want to do it I should use nested queries that is not a good choice ... 🙁

Is there any way to avoid that?

Best Regards,
Ashkan

ashkan sirous SSCrazy Points: 2733 More actions · Answer 13

This is the Original function body:

DECLARE @POIid int

set @POIid= (select top 1 DMSPurposeOfIssueTbl.POIDescription

from DMSRequestDocListTbl

inner join DMSRequestTbl on DMSRequestDocListTbl.RequestCode = DMSRequestTbl.RequestCode

and RequestTypeID in ( 8,9) and DocRevID = @DocRevID

inner join DMSPurposeOfIssueTbl on DMSRequestDocListTbl.POIid = DMSPurposeOfIssueTbl.POIid

order by DMSRequestTbl.RequestCode desc)

-- Return the result of the function

RETURN @POIid

Best Regards,
Ashkan

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 14

FROM

.

CROSS APPLY (

SELECT poi.POIDescription, rn = ROW_NUMBER() OVER(ORDER BY r.RequestCode desc)

FROM DMSRequestDocListTbl rdl

INNER JOIN DMSRequestTbl r

ON rdl.RequestCode = r.RequestCode

AND r.RequestTypeID in ( 8,9)

AND r.DocRevID = @DocRevID -- << correlate @DocRevID to a column in a "table" in the FROM list

INNER JOIN DMSPurposeOfIssueTbl poi

ON rdl.POIid = poi.POIid

) poi

.

WHERE poi.rn = 1

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden