Query syntax

I have written a table value function defined as:
CREATE FUNCTION [dbo].[ReadControlMeasurement]
(
	-- Add the parameters for the function here
	@pat_id int,
	@StartMonth int,
	@EndMonth int 
)

Open in new window

I want to call it for all the patients in my database that are women.

i can get all the women from the database using

select pat_id from Patients where gender = 'W'

Open in new window

i was thinking along the lines of using a cte:

with Women as
( select distinct pat_id from Patients where Gender = 'W' )

select B.* from Women W inner join dbo.ReadControlMeasurement( W.pat_id, 0, 1 ) B on W.pat_id = B.pat_id

Open in new window

but this does not seem to have a valid syntax.

Any ideas?
soozhCEOAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Try using CROSS APPLY instead of INNER JOIN
0
soozhCEOAuthor Commented:
when i use cross apply the Query seems to take for ever
0
sarabhaiCommented:
Can you explain what you want to achieve.
 Also with ReadControlMeasurement function definition
0
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

Vitor MontalvãoMSSQL Senior EngineerCommented:
Can't really understand the need for a function. And that function as you posted it's malformed.
0
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
It would take for ever. That's because using a function within a query is not very fast.

Try rewriting the function as a view, perhaps, or maybe just a single query,
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
soozhCEOAuthor Commented:
there was an error in my function but after i fixed that Cross apply worked
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.