Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 1234»»»

UDF Help Expand / Collapse
Author
Message
Posted Friday, February 22, 2013 9:18 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 1:07 AM
Points: 235, Visits: 912
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
Posted Friday, February 22, 2013 10:36 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 9:36 PM
Points: 20,738, Visits: 32,522
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.



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
Posted Sunday, February 24, 2013 8:24 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued 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
Posted Monday, February 25, 2013 6:05 PM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Yesterday @ 9:51 PM
Points: 612, Visits: 2,858

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.


-- Alan Burstein



Read this article for best practices on asking questions.
Need to split a string? Try this (Jeff Moden)
Need a pattern-based string spitter? Try this (Dwain Camps)

"I can't 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. " -- Itzek Ben-Gan 2001

My blog
Post #1423833
Posted Monday, February 25, 2013 10:17 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:38 PM
Points: 35,371, Visits: 31,912
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1423880
Posted Wednesday, February 27, 2013 6:33 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 2:18 PM
Points: 4,406, Visits: 6,268
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
Posted Wednesday, February 27, 2013 6:47 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:54 AM
Points: 6,813, Visits: 14,028
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
Posted Wednesday, February 27, 2013 6:54 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:38 PM
Points: 35,371, Visits: 31,912
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1424515
Posted Wednesday, February 27, 2013 6:56 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:54 AM
Points: 6,813, Visits: 14,028
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
Exploring Recursive CTEs by Example Dwain Camps
Post #1424516
Posted Wednesday, February 27, 2013 7:13 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 2:18 PM
Points: 4,406, Visits: 6,268
. . .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!


Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #1424524
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse