Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
nmg196
Yak Posting Veteran
70 Posts |
 Posted - 2002-05-28 : 12:51:42
|
Hi,I have a complex stored procedure that returns a list of IDs (primary keys). This stored procedure usually completes pretty quickly.My application then uses this list to pass into a new dynamically generated SQL query:SELECT * ID FROM VenueWHERE Venue.primaryKey IN (59,304,1717,1791,1999,2270,2790,3120)AND [lots of additional clauses here] The only problem is, this query seems to be extremely slow (a minute or so) when the stored procedure returns *lots* of Venues (a few thousand). ie, where the 'IN' list is very long.Is there a more efficient way of doing this, other than passing in this list? Or is there a way to use the results of a stored procedure directly in an SQL statement?Thanks,Nick... |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-05-28 : 13:08:22
|
| if you are on sql2k, use a table-valued user-defined function to provide your rowset of IDs. if you are not on sql2k, use the insert...exec... syntax to set up a temp table with you rowset. the (in eithercase) join to your venue table on id.<O> |
 |
|
|
M.E.
Aged Yak Warrior
539 Posts |
Posted - 2002-05-28 : 13:29:51
|
| Would it be possible to set a variable to the output of a stored proc select @var = (exec storedproc)and then just use this var for the where in statement? |
|
|
|
JustinBigelow
SQL Gigolo
1157 Posts |
Posted - 2002-05-28 : 15:47:43
|
quote:
Would it be possible to set a variable to the output of a stored proc select @var = (exec storedproc)and then just use this var for the where in statement?
Not possible, the proper method of assigning a var to the result of a stored procedure is to use an output parameter, and if the result set is nonscalar a csv string would have to be built for your example to work. Sounds like this is what Nick is already doing. Nick, do you have any indexes on the tables? Have you checked the query plan to see if there are any table/index scans you could possibly refine to seeks? How about creating a second stored procedure to pass the IN list to so that the query plan is cached after the first compile.just a thought,Justin |
|
|
|
nmg196
Yak Posting Veteran
70 Posts |
Posted - 2002-05-29 : 05:07:58
|
| Sorry - I don't get it..How can I insert the results of a stored procedure into a temp table? The usual syntax doesn't appear to work.Why on earth doesn't SQL Server let you do this:SELECT ID FROM VenueWHERE Venue.primaryKey IN (spGetVenuesByRooms '343')I'm still very stuck :( |
|
|
|
nmg196
Yak Posting Veteran
70 Posts |
Posted - 2002-05-29 : 05:36:20
|
It's OK I've got it...I needed this code:create table #requirements (venueid int)insert #requirementsexec spGetVenuesByMeetingRooms 'B,T', '20,60'select id from Venue where id in (select venueid from #requirements) and [...etc]drop table #requirements  Thanks for your help...Nick...Edited by - nmg196 on 05/29/2002 05:37:51 |
|
|
|
|
|
|
|
|
I need WHERE IN [results of a stored procedure]