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 |
|
BigMeat
Yak Posting Veteran
56 Posts |
 Posted - 2007-12-23 : 23:23:09
|
| Hi I have a stored procedure that does a post code lookup of addresses, I want to find all Addresses within a specific range of PostCodes. I have stored procedure (uspPostCodesWithinMyArea) that does some complex math to find all local post codes that are within my region and returns them as a list. Is it possible to call this procedure from my Sub Select. For exampleSELECT Name, Address1WHERE PostCode IN (exec uspPostCodesWithinMyArea @Latitude @Longitude)My procedure uspPostCodesWithinMyArea is called from lots of places so it would be nice to keep this encapsulated by it self, and not cut and paste this every whereThe only other way I thought of doing this was to put the list from uspPostCodesWithinMyArea into a temp table, but this makes the query much slower.Any help would be much appreciated |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2007-12-24 : 00:54:11
|
| I think since you need to call this at many places its better to put results onto temp table and take inner join wit temp table at whatever places you want to filter rather than calling SP everywhere. Try putting an index on PostCode column of temp table and main table. the above query will then become:-SELECT Name, Address1FROM Table tINNER JOIN #temp tmpON t.PostCode=tmp.PostCode |
 |
|
|
BigMeat
Yak Posting Veteran
56 Posts |
Posted - 2007-12-24 : 01:26:24
|
| Hi, thanks for the replyThe problem that im having with the temp table is that it takes too long to populate, as in some cases im inserting 30,0000 records. The insert command seems to be the bottle neckIm doing a basic insert into #temp to populate it, could I speed this up some how?many thanks once again |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2007-12-24 : 01:32:41
|
| http://sqlblogcasts.com/blogs/madhivanan/archive/2007/11/26/select-columns-from-exec-procedure-name-is-this-possible.aspxMadhivananFailing to plan is Planning to fail |
|
|
|
BigMeat
Yak Posting Veteran
56 Posts |
Posted - 2007-12-24 : 01:53:50
|
| Hi,Thanks for the link, is there anyway to use the OpenRowSet method without having to to hard code the database name, its just that this stored proc will be used on serveral different databasesthanks |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
|
|
|
|
|
|
|
use EXEC in sub query