I need help with a scalar function.

Question

Post reply

I need help with a scalar function.

NineIron

SSChampion

Points: 12529
More actions
February 9, 2016 at 5:53 am

#307855

When I try to create/alter this function, I get the following error. I included my code for the function and some test data.
Any thoughts on the function?
Msg 102, Level 15, State 1, Procedure fnPressGaneyDownload, Line 23
Incorrect syntax near 'RETURNS'.
Msg 178, Level 15, State 1, Procedure fnPressGaneyDownload, Line 49
A RETURN statement with a return value cannot be used in this context.
alter FUNCTION [dbo].[fnPressGaneyDownload]
RETURNS VarChar(100) as
BEGIN
DECLARE @Return VarChar(100)
select @Return=case when RA_M.RegistrationType_MisRegTypeID='ER' then 'ER0101'
when RA_M.RegistrationType_MisRegTypeID='SDC' then 'AS0101'
when RA_M.Location_MisLocID in('ENDO','CCATH') then 'AS0101'
when RA_M.Location_MisLocID='B4' then 'IN0102'
when RA_M.Location_MisLocID='N L2' then 'IN0104'
when RA_M.RegistrationType_MisRegTypeID='IN' and MP_PP.ProviderPractice_MisProvGrpID='BHHLIST' then 'IN0101'
when RA_M.RegistrationType_MisRegTypeID='IN' and MP_PP.ProviderPractice_MisProvGrpID<>'BHHLIST' then 'IN0103'
when RA_M.RegistrationType_MisRegTypeID='INO' and MP_PP.ProviderPractice_MisProvGrpID='BHHLIST' then 'IN0101'
when RA_M.RegistrationType_MisRegTypeID='INO' and MP_PP.ProviderPractice_MisProvGrpID<>'BHHLIST' then 'IN0103'
else 'IN0101'
end
from testfdb.dbo.RegAcct_Main RA_M
inner join testfdb.dbo.RegAcct_Providers RA_P
on RA_M.SourceID=RA_P.SourceID
and RA_M.VisitID=RA_P.VisitID
and RA_P.IsAttendingProvider = 'Y'
inner join testfdb.dbo.MisPerson_ProviderPractices MP_PP
on RA_P.SourceID=MP_PP.SourceID
and RA_P.Provider_UnvUserID=MP_PP.UnvUserID
RETURN Coalesce(@Return,'');
END
create table #Test
(
VisitID varchar(50),
RegType varchar(10),
Location varchar(10),
MDGroup varchar(25)
)
insert into #Test(VisitID, RegType, Location,MDGroup) values('CS0-B20151214085226492','REF','CAP','VRAD')
insert into #Test(VisitID, RegType, Location,MDGroup) values('F0-B20150407092249996','CLI','PRIM3','BHER')
insert into #Test(VisitID, RegType, Location,MDGroup) values('F0-B20150407092249996','CLI','PRIM3','BHHLIST')
insert into #Test(VisitID, RegType, Location,MDGroup) values('F0-B20150407092252354','CLI','BHASTH','BCARDIOLOG')
insert into #Test(VisitID, RegType, Location,MDGroup) values('F0-B20150407092303533','CLI','BHMRI','BHER')
insert into #Test(VisitID, RegType, Location,MDGroup) values('F0-B20150407092303533','CLI','BHMRI','BHHLIST')
insert into #Test(VisitID, RegType, Location,MDGroup) values('F0-B20150407095857886','REF','ABLAB','BHER')
insert into #Test(VisitID, RegType, Location,MDGroup) values('F0-B20150407095857886','REF','ABLAB','BHHLIST')
insert into #Test(VisitID, RegType, Location,MDGroup) values('F0-B20150408091254019','REF','BHLAB','BHER')
insert into #Test(VisitID, RegType, Location,MDGroup) values('F0-B20150408091254019','REF','BHLAB','BHHLIST')
insert into #Test(VisitID, RegType, Location,MDGroup) values('F0-B20150408130415007','REF','BHLABSPECPU','BHER')
insert into #Test(VisitID, RegType, Location,MDGroup) values('F0-B20150408130415007','REF','BHLABSPECPU','BHHLIST')
insert into #Test(VisitID, RegType, Location,MDGroup) values('F0-B20150408131733460','REF','BHLAB','BHER')
insert into #Test(VisitID, RegType, Location,MDGroup) values('F0-B20150408131733460','REF','BHLAB','BHHLIST')
insert into #Test(VisitID, RegType, Location,MDGroup) values('F0-B20150408131800901','REF','BHLAB','BHER')
insert into #Test(VisitID, RegType, Location,MDGroup) values('F0-B20150408131800901','REF','BHLAB','BHHLIST')
insert into #Test(VisitID, RegType, Location,MDGroup) values('F0-B20150408133008595','IN','EDOBV','BHER')
insert into #Test(VisitID, RegType, Location,MDGroup) values('F0-B20150408133008595','IN','EDOBV','BHHLIST')
insert into #Test(VisitID, RegType, Location,MDGroup) values('F0-B20150409082051657','REF','BHLAB','BHER')
insert into #Test(VisitID, RegType, Location,MDGroup) values('F0-B20150409082051657','REF','BHLAB','BHHLIST')
insert into #Test(VisitID, RegType, Location,MDGroup) values('F0-B20150424131620525','IN','A3TCU','BHER')
insert into #Test(VisitID, RegType, Location,MDGroup) values('F0-B20150424131620525','IN','A3TCU','BHHLIST')
insert into #Test(VisitID, RegType, Location,MDGroup) values('F0-B20150513091223474','REF','BHRAD','BCARDIOLOG')
insert into #Test(VisitID, RegType, Location,MDGroup) values('F0-B20150513102105808','REF','BHLABRAD','BHER')
insert into #Test(VisitID, RegType, Location,MDGroup) values('F0-B20150513102105808','REF','BHLABRAD','BHHLIST')
insert into #Test(VisitID, RegType, Location,MDGroup) values('F0-B20150526120049691','INO','P2','BHHLIST')
insert into #Test(VisitID, RegType, Location,MDGroup) values('F0-B20150603120041630','RCR','BHREHAB','BHHLIST')
insert into #Test(VisitID, RegType, Location,MDGroup) values('F0-B20150610120234389','INO','A2','BIDMCGYN')
insert into #Test(VisitID, RegType, Location,MDGroup) values('F0-B20150616191720377','REF','BH3SUT','BIDMCGYN')
insert into #Test(VisitID, RegType, Location,MDGroup) values('F0-B20150619060758823','CLI','BHLABRAD','BIDMCGYN')
insert into #Test(VisitID, RegType, Location,MDGroup) values('F0-B20150623110929464','RCR','BHST','BHHLIST')
insert into #Test(VisitID, RegType, Location,MDGroup) values('F0-B20150624114948828','REF','BHLABRAD','BHHLIST')
insert into #Test(VisitID, RegType, Location,MDGroup) values('F0-B20150625112115205','RCR','BHREHAB','BHHLIST')
insert into #Test(VisitID, RegType, Location,MDGroup) values('F0-B20150625160107628','REF','BHPAIN','BHHLIST')
insert into #Test(VisitID, RegType, Location,MDGroup) values('F0-B20150626143025832','IN','B2','BHER')
insert into #Test(VisitID, RegType, Location,MDGroup) values('F0-B20150626143025832','IN','B2','BHHLIST')
insert into #Test(VisitID, RegType, Location,MDGroup) values('F0-B20150629073624562','IN','N1','BIDMCGYN')
insert into #Test(VisitID, RegType, Location,MDGroup) values('F0-B20150629102536706','REF','PAIN','BHHLIST')
insert into #Test(VisitID, RegType, Location,MDGroup) values('F0-B20150630140607284','IN','C3','BIDMCGYN')
insert into #Test(VisitID, RegType, Location,MDGroup) values('F0-B20150730091103440','IN','A2','BHER')
insert into #Test(VisitID, RegType, Location,MDGroup) values('F0-B20150730091103440','IN','A2','BHHLIST')
insert into #Test(VisitID, RegType, Location,MDGroup) values('F0-B20150730092128798','IN','A2','BHER')
insert into #Test(VisitID, RegType, Location,MDGroup) values('F0-B20150730092128798','IN','A2','BHHLIST')
insert into #Test(VisitID, RegType, Location,MDGroup) values('F0-B20150730100812221','REF','BHCT','BHER')
insert into #Test(VisitID, RegType, Location,MDGroup) values('F0-B20150730100812221','REF','BHCT','BHHLIST')
insert into #Test(VisitID, RegType, Location,MDGroup) values('F0-B20150730103530054','SDC','ENDOSDC','BHER')
insert into #Test(VisitID, RegType, Location,MDGroup) values('F0-B20150730103530054','SDC','ENDOSDC','BHHLIST')
insert into #Test(VisitID, RegType, Location,MDGroup) values('F0-B20150803131914697','IN','A2','EYE')
insert into #Test(VisitID, RegType, Location,MDGroup) values('F0-B20150812084915546','IN','CPOU','BHHLIST')
insert into #Test(VisitID, RegType, Location,MDGroup) values('F0-B20150812102340950','INO','A5','SMG')
insert into #Test(VisitID, RegType, Location,MDGroup) values('F0-B20150817080538137','IN','A2','NEMC')
insert into #Test(VisitID, RegType, Location,MDGroup) values('F0-B20150825143117571','RCR','BHREHAB','BHHLIST')
insert into #Test(VisitID, RegType, Location,MDGroup) values('F0-B20150825161912682','REF','ABLAB','CM')
insert into #Test(VisitID, RegType, Location,MDGroup) values('F0-B20150826083115755','IN','A2','BHHLIST')
insert into #Test(VisitID, RegType, Location,MDGroup) values('F0-B20150827105455121','IN','A2','BHHLIST')
insert into #Test(VisitID, RegType, Location,MDGroup) values('F0-B20150827110515876','IN','A2','BHHLIST')
insert into #Test(VisitID, RegType, Location,MDGroup) values('F0-B20150827114241512','IN','A4MEDSUR','BHHLIST')
insert into #Test(VisitID, RegType, Location,MDGroup) values('F0-B20150827115340402','IN','B2','BHHLIST')
insert into #Test(VisitID, RegType, Location,MDGroup) values('F0-B20150827141049571','CLI','ENDOSDC','BHHLIST')
insert into #Test(VisitID, RegType, Location,MDGroup) values('F0-B20150827141752469','CLI','SDC','BHHLIST')
insert into #Test(VisitID, RegType, Location,MDGroup) values('F0-B20150827142643718','REF','SDC','BHHLIST')
insert into #Test(VisitID, RegType, Location,MDGroup) values('F0-B20150827143305166','REF','BHLABRAD','BHHLIST')
insert into #Test(VisitID, RegType, Location,MDGroup) values('F0-B20150827143752922','IN','A4','BHHLIST')
insert into #Test(VisitID, RegType, Location,MDGroup) values('F0-B20150827144449985','IN','C3','BHHLIST')
insert into #Test(VisitID, RegType, Location,MDGroup) values('F0-B20150827145255635','SDC','SDC','BHHLIST')
insert into #Test(VisitID, RegType, Location,MDGroup) values('F0-B20150827145859596','SDC','SDC','BHHLIST')
insert into #Test(VisitID, RegType, Location,MDGroup) values('F0-B20150827150412405','IN','A2','BHHLIST')
insert into #Test(VisitID, RegType, Location,MDGroup) values('F0-B20150827150944590','IN','A5','BHHLIST')
insert into #Test(VisitID, RegType, Location,MDGroup) values('F0-B20150827152800082','REF','BHLABRAD','BHHLIST')
insert into #Test(VisitID, RegType, Location,MDGroup) values('F0-B20150827153300052','REF','BHLABRAD','BHHLIST')
insert into #Test(VisitID, RegType, Location,MDGroup) values('F0-B20150827154757811','IN','A5','BHHLIST')
insert into #Test(VisitID, RegType, Location,MDGroup) values('F0-B20150827155419431','IN','A5','BHHLIST')
insert into #Test(VisitID, RegType, Location,MDGroup) values('F0-B20150827160130940','CLI','BHCCATH','BHHLIST')
insert into #Test(VisitID, RegType, Location,MDGroup) values('F0-B20150827160611364','CLI','SDC','BHHLIST')
insert into #Test(VisitID, RegType, Location,MDGroup) values('F0-B20150828124653586','IN','B2','BHHLIST')
insert into #Test(VisitID, RegType, Location,MDGroup) values('F0-B20150828125601611','IN','CCU','BHHLIST')
insert into #Test(VisitID, RegType, Location,MDGroup) values('F0-B20150828130608177','IN','B4','BHHLIST')
insert into #Test(VisitID, RegType, Location,MDGroup) values('F0-B20150828131218063','IN','CCU','BHHLIST')
insert into #Test(VisitID, RegType, Location,MDGroup) values('F0-B20150828131844426','IN','A2','BHHLIST')
insert into #Test(VisitID, RegType, Location,MDGroup) values('F0-B20150828132514049','IN','A2','BHHLIST')
insert into #Test(VisitID, RegType, Location,MDGroup) values('F0-B20150828133045875','IN','B2','BHHLIST')
insert into #Test(VisitID, RegType, Location,MDGroup) values('F0-B20150828133707382','IN','B3','BHHLIST')
insert into #Test(VisitID, RegType, Location,MDGroup) values('F0-B20150828134324463','IN','A2','BHHLIST')
insert into #Test(VisitID, RegType, Location,MDGroup) values('F0-B20150828141051425','IN','A5','BHHLIST')
insert into #Test(VisitID, RegType, Location,MDGroup) values('F0-B20150828141608553','IN','B2','BHHLIST')
insert into #Test(VisitID, RegType, Location,MDGroup) values('F0-B20150828142013891','IN','A2','BHHLIST')
insert into #Test(VisitID, RegType, Location,MDGroup) values('F0-B20150828142352321','IN','B2','BHHLIST')
insert into #Test(VisitID, RegType, Location,MDGroup) values('F0-B20150828142702734','IN','B4','BHHLIST')
insert into #Test(VisitID, RegType, Location,MDGroup) values('F0-B20150828143246041','IN','B3','BHHLIST')
insert into #Test(VisitID, RegType, Location,MDGroup) values('F0-B20150828143711691','IN','A5','BHHLIST')
insert into #Test(VisitID, RegType, Location,MDGroup) values('F0-B20150828144204751','IN','B3','BHHLIST')
insert into #Test(VisitID, RegType, Location,MDGroup) values('F0-B20150831154928581','CLI','BHCT','VRAD')
insert into #Test(VisitID, RegType, Location,MDGroup) values('F0-B20150831160634727','CLI','BHMRI','BOSMEDCTR')
insert into #Test(VisitID, RegType, Location,MDGroup) values('F0-B20150901104931081','IN','B2','BOSMEDCTR')
insert into #Test(VisitID, RegType, Location,MDGroup) values('F0-B20150901153323822','IN','EDOBV','MATRIX')
insert into #Test(VisitID, RegType, Location,MDGroup) values('F0-B20150901154436177','SDC','SDC','MATRIX')
insert into #Test(VisitID, RegType, Location,MDGroup) values('F0-B20150901155940467','SDC','SDC','MATRIX')
insert into #Test(VisitID, RegType, Location,MDGroup) values('F0-B20150901163022421','IN','B3','BHHLIST')
insert into #Test(VisitID, RegType, Location,MDGroup) values('F0-B20150901163316750','IN','A3TCU','BHHLIST')
insert into #Test(VisitID, RegType, Location,MDGroup) values('F0-B20150901191741318','IN','A5','SMG')

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

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

Lowell SSC Guru Points: 323504 More actions · Answer 1

easy fix.

if the function has no parameters, you still need the parenthesis for the parameter list.

alter FUNCTION [dbo].[fnPressGaneyDownload] ()

your function could easily be converted to an inline table value function, that would be multiple orders of magnitude faster; you would have to refactor some of your code to use cross apply instead of an inline scalar.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

NineIron SSChampion Points: 12529 More actions · Answer 2

NineIron

SSChampion

Points: 12529

February 9, 2016 at 6:24 am

#1857355

Thanx. I'm new at functions.

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

Be careful. Data-accessing scalar functions can be horrifically slow. Avoid using it in a select/insert/update/delete and it should be OK.

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

NineIron SSChampion Points: 12529 More actions · Answer 4

NineIron

SSChampion

Points: 12529

February 9, 2016 at 6:58 am

#1857367

k

Sean Lange SSC Guru Points: 286573 More actions · Answer 5

GilaMonster (2/9/2016)
Be careful. Data-accessing scalar functions can be horrifically slow. Avoid using it in a select/insert/update/delete and it should be OK.

LOL that is awesome Gail!!! 😀

_______________________________________________________________

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/

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 6

NineIron (2/9/2016)
k

Like this:

CREATE FUNCTION dbo.fnPressGaneyDownload()

RETURNS TABLE AS

RETURN (

SELECT CAST(

COALESCE( CASE WHEN RA_M.RegistrationType_MisRegTypeID='ER' then 'ER0101'

WHEN RA_M.RegistrationType_MisRegTypeID='SDC' then 'AS0101'

WHEN RA_M.Location_MisLocID in('ENDO','CCATH') then 'AS0101'

WHEN RA_M.Location_MisLocID='B4' then 'IN0102'

WHEN RA_M.Location_MisLocID='N L2' then 'IN0104'

WHEN RA_M.RegistrationType_MisRegTypeID='IN' and MP_PP.ProviderPractice_MisProvGrpID='BHHLIST' then 'IN0101'

WHEN RA_M.RegistrationType_MisRegTypeID='IN' and MP_PP.ProviderPractice_MisProvGrpID<>'BHHLIST' then 'IN0103'

WHEN RA_M.RegistrationType_MisRegTypeID='INO' and MP_PP.ProviderPractice_MisProvGrpID='BHHLIST' then 'IN0101'

WHEN RA_M.RegistrationType_MisRegTypeID='INO' and MP_PP.ProviderPractice_MisProvGrpID<>'BHHLIST' then 'IN0103'

ELSE 'IN0101'

END,'')

AS VARCHAR(100)) AS Result

from testfdb.dbo.RegAcct_Main RA_M

inner join testfdb.dbo.RegAcct_Providers RA_P on RA_M.SourceID=RA_P.SourceID

and RA_M.VisitID=RA_P.VisitID

and RA_P.IsAttendingProvider = 'Y'

inner join testfdb.dbo.MisPerson_ProviderPractices MP_PP on RA_P.SourceID=MP_PP.SourceID

and RA_P.Provider_UnvUserID=MP_PP.UnvUserID);

GO

Then you can use it like this:

SELECT Result FROM dbo.fnPressGaneyDownload()

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

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

Orlando Colamatteo (2/9/2016)
NineIron (2/9/2016)
k
Like this:
CREATE FUNCTION dbo.fnPressGaneyDownload()
RETURNS TABLE AS
RETURN (
SELECT CAST(
COALESCE( CASE WHEN RA_M.RegistrationType_MisRegTypeID='ER' then 'ER0101'
WHEN RA_M.RegistrationType_MisRegTypeID='SDC' then 'AS0101'
WHEN RA_M.Location_MisLocID in('ENDO','CCATH') then 'AS0101'
WHEN RA_M.Location_MisLocID='B4' then 'IN0102'
WHEN RA_M.Location_MisLocID='N L2' then 'IN0104'
WHEN RA_M.RegistrationType_MisRegTypeID='IN' and MP_PP.ProviderPractice_MisProvGrpID='BHHLIST' then 'IN0101'
WHEN RA_M.RegistrationType_MisRegTypeID='IN' and MP_PP.ProviderPractice_MisProvGrpID<>'BHHLIST' then 'IN0103'
WHEN RA_M.RegistrationType_MisRegTypeID='INO' and MP_PP.ProviderPractice_MisProvGrpID='BHHLIST' then 'IN0101'
WHEN RA_M.RegistrationType_MisRegTypeID='INO' and MP_PP.ProviderPractice_MisProvGrpID<>'BHHLIST' then 'IN0103'
ELSE 'IN0101'
END,'')
AS VARCHAR(100)) AS Result
from testfdb.dbo.RegAcct_Main RA_M
inner join testfdb.dbo.RegAcct_Providers RA_P on RA_M.SourceID=RA_P.SourceID
and RA_M.VisitID=RA_P.VisitID
and RA_P.IsAttendingProvider = 'Y'
inner join testfdb.dbo.MisPerson_ProviderPractices MP_PP on RA_P.SourceID=MP_PP.SourceID
and RA_P.Provider_UnvUserID=MP_PP.UnvUserID);
GO
Then you can use it like this:
SELECT Result FROM dbo.fnPressGaneyDownload()

I wonder how many rows this returns? If it's only one, then the original scalar function returning the result to a variable would have been just fine - and it would have met Gail's precautionary restrictions too. If it returns more than one row then a parameter or two might be in order.

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

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

ChrisM@Work (2/10/2016)
and it would have met Gail's precautionary restrictions too.

My comments had nothing to do with what the function returns, rather where to use it (not in a DML statement affecting more than a couple of rows)

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

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

GilaMonster (2/10/2016)
ChrisM@Work (2/10/2016)
and it would have met Gail's precautionary restrictions too.
My comments had nothing to do with what the function returns, rather where to use it (not in a DML statement affecting more than a couple of rows)

My comment was also where to use it: "returning the result to a variable".

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

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

ChrisM@Work (2/10/2016)
GilaMonster (2/10/2016)
ChrisM@Work (2/10/2016)
and it would have met Gail's precautionary restrictions too.
My comments had nothing to do with what the function returns, rather where to use it (not in a DML statement affecting more than a couple of rows)
My comment was also where to use it: "returning the result to a variable".

Insufficient coffee error. 🙁

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

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

GilaMonster (2/10/2016)
ChrisM@Work (2/10/2016)
GilaMonster (2/10/2016)
ChrisM@Work (2/10/2016)
and it would have met Gail's precautionary restrictions too.
My comments had nothing to do with what the function returns, rather where to use it (not in a DML statement affecting more than a couple of rows)
My comment was also where to use it: "returning the result to a variable".
Insufficient coffee error. 🙁

Gail - I'm far more familiar with this error than you are!

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

NineIron SSChampion Points: 12529 More actions · Answer 12

NineIron

SSChampion

Points: 12529

February 10, 2016 at 4:28 am

#1857634

You guys are the best.

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 13

ChrisM@Work (2/10/2016)
If it's only one, then the original scalar function returning the result to a variable would have been just fine - and it would have met Gail's precautionary restrictions too. If it returns more than one row then a parameter or two might be in order.

Gail's advice is good, as usual. I wasn't disputing anything that was said.

Like a lot of folks, I suggest choosing inline tvf by default. Unless you're up against one of the technical limitations in SQL Server or have an awkward data-requirement forcing the use of a scalar valued function or multi-statement tvf then I opt not to bring scalar functions into this world.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

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

Orlando Colamatteo (2/10/2016)
ChrisM@Work (2/10/2016)
If it's only one, then the original scalar function returning the result to a variable would have been just fine - and it would have met Gail's precautionary restrictions too. If it returns more than one row then a parameter or two might be in order.
Gail's advice is good, as usual. I wasn't disputing anything that was said.
Like a lot of folks, I suggest choosing inline tvf by default. Unless you're up against one of the technical limitations in SQL Server or have an awkward data-requirement forcing the use of a scalar valued function or multi-statement tvf then I opt not to bring scalar functions into this world.

That's a lot of ifs and buts. Jeff is fond of pointing out that programmers are idle and tend to copy stuff - even when it's not necessarily good stuff. There's another but. This might be a really odd case where querying a bunch of tables without any parameters always returns a scalar value. If - and it's a big if - there's an existing scalar function which does the job and is called in exactly the way I suggested, then (at least here) I'd face resistance arguing for change.

How would I write it from scratch? As an iTVF of course.

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