UDF Help

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » UDF Help

35 posts, Page 1 of 4

1 2 3 4 »»»

UDF Help

Rate Topic

Display Mode

Topic Options

Author

Message

rocky_498

Posted Friday, February 22, 2013 9:18 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, June 14, 2013 11:56 AM
Points: 149, Visits: 561

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?

Post #1423315

Lynn Pettis

Posted Friday, February 22, 2013 10:36 PM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 1:13 PM
Points: 21,832, Visits: 27,850

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.

Cool

Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)

Post #1423321

dgowrij

Posted Sunday, February 24, 2013 8:24 PM

Valued Member

Group: General Forum Members
Last Login: Thursday, March 28, 2013 5:32 AM
Points: 50, Visits: 75

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

Post #1423461

Alan.B

Posted Monday, February 25, 2013 6:05 PM

SSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 9:38 AM
Points: 237, Visits: 1,195

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.

-- AJB
xmlsqlninja.com

Post #1423833

Jeff Moden

Posted Monday, February 25, 2013 10:17 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:19 PM
Points: 33,110, Visits: 27,035

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

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #1423880

TheSQLGuru

Posted Wednesday, February 27, 2013 6:33 AM

Hall of Fame

Group: General Forum Members
Last Login: Today @ 11:35 AM
Points: 3,672, Visits: 5,172

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 at GMail

Post #1424497

ChrisM@Work

Posted Wednesday, February 27, 2013 6:47 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 9:11 AM
Points: 5,705, Visits: 11,132

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
Exploring Recursive CTEs by Example Dwain Camps

Post #1424506

Jeff Moden

Posted Wednesday, February 27, 2013 6:54 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:19 PM
Points: 33,110, Visits: 27,035

TheSQLGuru (2/27/2013)

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

--Jeff Moden

Post #1424515

ChrisM@Work

Posted Wednesday, February 27, 2013 6:56 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 9:11 AM
Points: 5,705, Visits: 11,132

Jeff Moden (2/27/2013)

TheSQLGuru (2/27/2013)

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}

Post #1424516

TheSQLGuru

Posted Wednesday, February 27, 2013 7:13 AM

Hall of Fame

Group: General Forum Members
Last Login: Today @ 11:35 AM
Points: 3,672, Visits: 5,172

. . .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 at GMail

Post #1424524

« Prev Topic | Next Topic »

35 posts, Page 1 of 4

1 2 3 4 »»»

Permissions