SQLTeam.com | Weblogs | Forums

Stored function in select query


#1

HI,

I have the below select query to retrieve data which i need.

'Medicalcondition' for each policies are stored in a test column where i have a stored function [dbo].[getConditions] to extract the 'condition part' from the long text.

If i execute - select [dbo].[getConditions] ('MEDDEC907'),
i willl get 'Mild Asthma' as result.

I want to include this [dbo].[getcondition] function in select query to retrieve corresponding medical condition for each policynumber. How to include this in select query to retrieve condition for each policynumber.

Any help will be highly appreciated.

SELECT
MedicalScreening.PolicyNumber
,Traveller.Title
,Traveller.FirstName
,Traveller.LastName
,Traveller.Age
,MedicalScreening.MedicalScore
from MedicalScreening
inner join Traveller
on Traveller.TravellerId = MedicalScreening.TravellerId

Thank You

Regards,
SG


#2

For the best performance - you need to convert the scalar function you have to an inline-table valued function. Once you have that then you can use it with OUTER/CROSS APPLY as in:

SELECT 
MedicalScreening.PolicyNumber
,Traveller.Title 
,Traveller.FirstName
,Traveller.LastName 
,Traveller.Age 
,MedicalScreening.MedicalScore 
,cond.Description
from MedicalScreening
inner join Traveller
on Traveller.TravellerId = MedicalScreening.TravellerId
OUTER APPLY dbo.getConditions(MedicalScreening.PolicyNumber) cond

Better option - since that function is probably just a simple lookup would be to join to the condition table directly as in:

JOIN conditions c ON c.PolicyNumber = MedicalScreening.PolicyNumber

Without seeing the actual function you built - I couldn't say which way would be best.