can you join on a stored procedure?

Question

I have a bunch of WITH statements:

;with OneAccession as (
        select client_id,COUNT(patient_id) PatientCount from
        (
            select client_id,patient_id
            from F_ACCESSION_DAILY
            group by CLIENT_ID,PATIENT_ID
            having COUNT(ACCESSION_ID)=1
        ) a
        group by CLIENT_ID
    )
    ,

    TwoAccessions as (
    select client_id,COUNT(patient_id) PatientCount from
        (
            select client_id,patient_id
            from F_ACCESSION_DAILY
            group by CLIENT_ID,PATIENT_ID
            having COUNT(ACCESSION_ID)=2
        ) a
    group by client_id
    )

    ,

    ThreeAccessions as (
    select client_id,COUNT(patient_id) PatientCount from
    (
        select client_id,patient_id
        from F_ACCESSION_DAILY
        group by CLIENT_ID,PATIENT_ID
        having COUNT(ACCESSION_ID)=3
    ) a
    group by client_id
    )
etc

And I join these statements on

select * from myTable
join OneAccession
on...
join TwoACcessions
on...
join ThreeAccessions

Instead of having all those with statements, can i just create a stored proc? I would just pass the value of having count(accession_id)=**@myParam** and do this:

select * from myTable
join storedproc(1)
on...
join storedproc(2)
on...
etc...

Is there an issue on joining on a stored Proc? Is my methodology OK?

score 3 · Answer 1 · answered Oct 18 '12 at 22:26

3

Have a look at APPLY. Using APPLY with table-valued functions seems to be the classic example for using APPLY, and I think it's what you want.

Have a look at this blog post with an example (using AdventureWorks):

select f.FirstName
      ,f.LastName
      ,f.JobTitle
      ,f.ContactType
      ,cc.CardNumber
from Sales.CreditCard cc
join Sales.ContactCreditCard ccc on cc.CreditCardID=ccc.CreditCardID
cross apply dbo.ufnGetContactInformation(ccc.ContactID) f
where cc.ExpYear=2008
  and cc.ExpMonth=6
  and cc.CardType='Vista'

answered Oct 18 '12 at 22:26

bhamby

15,112
1
45
66

and what are your thoughts on this solution? http://stackoverflow.com/a/2563175/117700 – Alex Gordon Oct 18 '12 at 22:27
I think the correct question would be `"What is the diff between TVF and stored proc"`, which is answered here: http://stackoverflow.com/questions/4254814/sql-server-table-valued-functions-vs-stored-procedures – bhamby Oct 18 '12 at 22:29

score 1 · Answer 2 · answered Oct 18 '12 at 22:26

1

No...you can do this using a table function instead though.

answered Oct 18 '12 at 22:26

PseudoToad

1,504
1
16
34

See this post: http://www.codeproject.com/Articles/167399/Using-Table-Valued-Functions-in-SQL-Server. Once the table function is created you can use it like a table using joins/applys. – PseudoToad Oct 18 '12 at 22:35

jmoreno · Accepted Answer · 2012-10-18T22:43:39.953

1

You can not join on a stored procedure, but you can join both a function and a view. Note that a view can not take parameters and that a function may not be as performant as the CTE.

Also, looking at your query, it looks like you should look into the new windowing functions and that something like

;with cte as (
    select *, count(*) over (partition by client_id, patient_id) patientcount
    from f_accession_daily
)
select * from myTable
     inner join cte on ... and patientCount=1

might help with what you are trying to achieve.

edited Oct 18 '12 at 22:43

answered Oct 18 '12 at 22:32

jmoreno

12,752
4
60
91

thanks so much can you please show me how select *, count(*)....is equivalent to having count...? – Alex Gordon Oct 18 '12 at 22:35
Added some code that shows the simularity. As others have pointed out, a TVF is what you are describing, but given your code I would spend some time seeing if there's not a different approach. Without knowing your schema and data I of course can't say for sure, but I think it's unlikely that your approach is the best solution to whatever problem you are attempting to solve. – jmoreno Oct 18 '12 at 22:48
im sorry, what would i be joining on and what would i be counting? – Alex Gordon Oct 18 '12 at 22:59
The count, for each row, is the number of rows with that patient and client id's, the difference s that instead of group by, you have partition by, and you can include any or all of the other columns in the table, without going back and doing a self join. – jmoreno Oct 18 '12 at 23:44

can you join on a stored procedure?

3 Answers3