Function inside a Stored Procedure

Question

Function inside a Stored Procedure

smallmoney

SSCarpal Tunnel

Points: 4378
More actions
April 28, 2011 at 10:18 am

#389407

I remember hearing that having functions in a stored procedure is no bueno.
In the past I would just use a subquery but I notice sometimes it looks cleaner to use a function.
ex:
CREATE PROCEDURE usp_name
AS
SET NOCOUNT ON
BEGIN
SELECTa.accountid,
a.name,
a.accountnumber,
DATEDIFF(YEAR, r.pa_originaljoindate, GETDATE()) AS years,
udf_CorporateAccountGetRepTotal(a.accountid) AS memberreps,
udf_CorporateAccountGetCIPTotal(a.accountid) AS cipreps,
udf_CorporateAccountGetCPTotal(a.accountid) AS cpreps
FROM Account a
INNER JOIN Renewalbilling r
ON a.accountid = r.accountid
END
Looks cleaner than
CREATE PROCEDURE usp_name
AS
SET NOCOUNT ON
BEGIN
SELECTa.accountid,
a.name,
a.accountnumber,
DATEDIFF(YEAR, r.pa_originaljoindate, GETDATE()) AS years,
(SELECT COUNT(*)......) AS memberreps,
(SELECT COUNT(*)......) AS cipreps,
(SELECT COUNT(*)......) AS cpreps
FROM Account a
INNER JOIN Renewalbilling r
ON a.accountid = r.accountid
END
I just remember hearing or reading briefly about functions are slow in stored procedures but I can't find any resources online. Thanks

Viewing 4 posts - 1 through 3 (of 3 total)

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

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 1

There is nothing wrong with using a function in a stored proc nowadays. However, there have always been issues with substituting subqueries with scalar functions that included select statements. The optimizer cannot correctly assess the workload of the queries embedded in the scalar function to come up with an efficient execution plan. While this was particularly true of functions that included select statements, there was also a lesser performance hit when the function just did calculations.

In 2005-2008, you can use inline table valued functions and avoid the performance penalties of scalar functions.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

CELKO (5/1/2011)
Really awful T-SQL people will use both a real key and an IDENTITY property. We do not use th4e old Sybase getdate() any more.

You have a very broad definition of 'really awful'. I certainly fall into it, so do many other good people. I do have my reasons and they are informed ones. Oh, and you may not use getdate any longer, but that certainly doesn't apply to everyone.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 3

CELKO (5/1/2011)
Instead of trying to make SQLK look like the procedural language you know...

BWAA-HAAA!!!! Listen to you "Mr. PushStack to make a Nested Set". And then you write a full blown Triangular Join which generates 500,000,000,000 internal rows to solve a simple problem like "Todd's Division" on a mere 1,000,000 rows at the following post...

http://www.sqlservercentral.com/Forums/FindPost1101199.aspx

... and you have the gall to talk about someone else using procedural code?

Stop being such a jerk, Joe. Clean up your own code first and then be nice to people because you're making the same and worse mistakes as they are.

--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)