• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 175
  • Last Modified:

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?
0
soozh
Asked:
soozh
2 Solutions
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Try using CROSS APPLY instead of INNER JOIN
0
 
soozhAuthor 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
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
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
 
soozhAuthor Commented:
there was an error in my function but after i fixed that Cross apply worked
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now