UDF Help

Question

UDF Help

rocky_498

SSCertifiable

Points: 6532
More actions
February 22, 2013 at 9:18 pm

#274530

Hi Guys,
I want to create UDF. Below is my logic. Please guide me where i am wrong.
Alter Function [dbo].[udf_GPList](@EID int,@PID int)
returns varchar(1000)
as
begin
Declare @Pist varchar(1000)=''
Declare @OID as varchar(2000) = ''
Declare @OTID as varchar(2000) = ''
SELECT
@OID = O.OId,
@OTID = O.OTId
From dbo.Order O
INNER JOIN dbo.PList PL ON PL.PId = O.PId
INNER JOIN dbo.Ens E ON E.EId = O.EId
WHERE PL.PId = @PID
AND E.EId = @Eid
SELECT
@Pist =
dbo.udfOrGetDetail (@OID,@OTID) +'<br />'
FROM dbo.PList TPL
INNER JOIN dbo.Ens E ON E.PId = TPL.PId
INNER JOIN dbo.Order O ON O.EId = E.EId
WHERE
E.EId = @EID
AND TPL.PId = @PID
Set @Pist= ISNULL(@Pist,'')
RETURN @Pist
END
Thanks in advance. If i am understand right. Function can return only one value. am i right?

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

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

I'm not even sure what you are trying to accomplish. Plus, what is the other function doing? There really isn't enough information to really provide much help.

You may want to read the first article I reference below in my signature block regarding asking for help. Follow the instructions in that article regarding what yu need to post and how to post it to get the best answers.

dgowrij SSC Veteran Points: 234 More actions · Answer 2

What is the problem you are facing when you create the UDF?

Alan Burstein SSC Guru Points: 61141 More actions · Answer 3

If i am understand right. Function can return only one value. am i right?

Scalar functions return one value, table valued functions return a table variable.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Jeff Moden SSC Guru Points: 1004367 More actions · Answer 4

Alan.B (2/25/2013)
If i am understand right. Function can return only one value. am i right?
Scalar functions return one value, table valued functions return a table variable.

Inline Table Valued Functions (iTVF for short) return a result set even if that result set is a single element. Think of it as an Inline Scalar Function. The "inline" type of function is about 7 times faster than any scalar function and also works faster for MultiLine Table Valued Fuctions.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 5

You may THINK you want to do a Scalar UDF, but you probably don't. I wrote a chapter for the SQL Server MVP Deep Dives 2 book entitled "Death by UDF". It is a very aptly named missive. Please do everything in your power to avoid the UDF and inline the code where you need it.

Oh, I haven't tested it, but I wonder if UDFs can call UDFs ...

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

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

The second query is a red herring. It will do this:

SELECT @Pist = dbo.udfOrGetDetail (@OID,@OTID) +'<br />'

as many times as rows returned by everything after the FROM list. Each time it runs it will generate exactly the same result, and then it will throw away all except the last result. As Kevin has pointed out, there is some scope for performance improvement here.

^{“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

Jeff Moden SSC Guru Points: 1004367 More actions · Answer 7

TheSQLGuru (2/27/2013)
You may THINK you want to do a Scalar UDF, but you probably don't. I wrote a chapter for the SQL Server MVP Deep Dives 2 book entitled "Death by UDF". It is a very aptly named missive. Please do everything in your power to avoid the UDF and inline the code where you need it.
Oh, I haven't tested it, but I wonder if UDFs can call UDFs ...

Yes they can. if they're not iTVFs, it about as joyous as an aggregated view calling an aggregated view.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

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

Jeff Moden (2/27/2013)
TheSQLGuru (2/27/2013)
You may THINK you want to do a Scalar UDF, but you probably don't. I wrote a chapter for the SQL Server MVP Deep Dives 2 book entitled "Death by UDF". It is a very aptly named missive. Please do everything in your power to avoid the UDF and inline the code where you need it.
Oh, I haven't tested it, but I wonder if UDFs can call UDFs ...
Yes they can. if they're not iTVFs, it about as joyous as an aggregated view calling an aggregated view.

Which could get very aggregating.

^{“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

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 9

. . .it about as joyous as an aggregated view calling an aggregated view.

You can do THAT TOO in SQL Server?!? Jeez, what a suck-@ss product! :w00t:

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Jeff Moden SSC Guru Points: 1004367 More actions · Answer 10

Jeff Moden (2/27/2013)
TheSQLGuru (2/27/2013)
You may THINK you want to do a Scalar UDF, but you probably don't. I wrote a chapter for the SQL Server MVP Deep Dives 2 book entitled "Death by UDF". It is a very aptly named missive. Please do everything in your power to avoid the UDF and inline the code where you need it.
Oh, I haven't tested it, but I wonder if UDFs can call UDFs ...
Yes they can. if they're not iTVFs, it about as joyous as an aggregated view calling an aggregated view.

I guess that about sums up this thead. 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ScottPletcher SSC Guru Points: 101105 More actions · Answer 11

TheSQLGuru (2/27/2013)
. . .it about as joyous as an aggregated view calling an aggregated view.
You can do THAT TOO in SQL Server?!? Jeez, what a suck-@ss product! :w00t:

No, those are "features", that add "flexibility" :-).

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jeff Moden SSC Guru Points: 1004367 More actions · Answer 12

TheSQLGuru (2/27/2013)
. . .it about as joyous as an aggregated view calling an aggregated view.
You can do THAT TOO in SQL Server?!? Jeez, what a suck-@ss product! :w00t:

BWAAA-HAAAA!!! 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Alan Burstein SSC Guru Points: 61141 More actions · Answer 13

Jeff Moden (2/25/2013)
Alan.B (2/25/2013)
If i am understand right. Function can return only one value. am i right?
Scalar functions return one value, table valued functions return a table variable.
Inline Table Valued Functions (iTVF for short) return a result set even if that result set is a single element. Think of it as an Inline Scalar Function. The "inline" type of function is about 7 times faster than any scalar function and also works faster for MultiLine Table Valued Fuctions.

I will confess, I have always used Scalar functions for returning a single value; I never thought to use an iTVF like that...

Unless I am misreading your comment - you are saying that scalar functions are worthless? Are there any cases where you would Scalar function over an iTVF that returns a single element?

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 14

Alan.B (2/28/2013)
Jeff Moden (2/25/2013)
Alan.B (2/25/2013)
If i am understand right. Function can return only one value. am i right?
Scalar functions return one value, table valued functions return a table variable.
Inline Table Valued Functions (iTVF for short) return a result set even if that result set is a single element. Think of it as an Inline Scalar Function. The "inline" type of function is about 7 times faster than any scalar function and also works faster for MultiLine Table Valued Fuctions.
I will confess, I have always used Scalar functions for returning a single value; I never thought to use an iTVF like that...
Unless I am misreading your comment - you are saying that scalar functions are worthless? Are there any cases where you would Scalar function over an iTVF that returns a single element?

I would honestly expect (but not certain here) that a scalar UDF that just puts it's output into a variable would be more efficient than iTVF that returns one row one column table simply due to less overhead related to the output format (variable vs table). However, if you use that scalar UDF directly in a SELECT/WHERE clause you can get totally hosed in several ways.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service