Solved

Query syntax

Posted on 2014-11-17
6
162 Views
Last Modified: 2014-11-18
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
Comment
Question by:soozh
6 Comments
 
LVL 24

Assisted Solution

by:Phillip Burton
Phillip Burton earned 500 total points
Comment Utility
Try using CROSS APPLY instead of INNER JOIN
0
 

Author Comment

by:soozh
Comment Utility
when i use cross apply the Query seems to take for ever
0
 
LVL 9

Expert Comment

by:sarabhai
Comment Utility
Can you explain what you want to achieve.
 Also with ReadControlMeasurement function definition
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
Can't really understand the need for a function. And that function as you posted it's malformed.
0
 
LVL 24

Accepted Solution

by:
Phillip Burton earned 500 total points
Comment Utility
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
 

Author Closing Comment

by:soozh
Comment Utility
there was an error in my function but after i fixed that Cross apply worked
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now