Software

Ask something
Discussion Locked

JOIN table and stored procedure

0 helpful
5 replies

by just_chilin 1 view

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

Is is possible to JOIN a table and the results of a stored procedure
(ANSI SQL, Oracle, MySQL or SQL Server)

5 replies

by mirrormirror

You would have to put the results of the stored proc in at least a temp table, then do the join. Is that what you are looking for? Do you have an example of what you are trying to accomplish?

by just_chilin

Consider a stored procedure sp_GetCustomers which I am trying to join with a table tbl_Complaints

i.e.
SELECT *
FROM sp_GetCustomers
INNER JOIN tbl_Complaints
ON sp_GetCustomers.CustomerID = tbl_Complaints.CustomerID

by tony hopkinson

Personally I'd just write an sp_GetCustomerComplaints procedure. As soon as you build up a substantial list of of customers or complaints you will have problems, the DBMS is not going to be able to optimize this query intelligently. You'd be better off cutting the content of the sp and then bolting on the join and submitting the entire thing. Doing this will negate much of the benefit of having a stored procedure in the first place.

by just_chilin

The problem is I am not allowed to create any new or modify any existing database objects. I could have written simple table joins but I have to use data from the stored procedure because it has been formatted and validated in a certain way.

Any suggestions?

by tony hopkinson

http://www.awprofessional.com/articles/article.asp?p=25288&seqNum=6
but you must have access to tempdb in sql server to do it.
I've been here though and this is a chocolate fireguard solution, as soon as things start warming up, you end up with a messy puddle.

It would take whoever owns the database about ten minutes to to add a new sp to do what you want.

If this is a vendor's database and you are doing an add on, it might be interesting to look at some sort of data copy to another db just for mis purposes, there would be more benefits than just the ownership.

This discussion is closed to new replies.